Monday, December 15, 2008

Database Historical Trend Analysis using OS/WEB Tiers

Database Historical Trend Analysis using OS/WEB Tiers:
In this artical, I will be concentrating on capturing different levels of reports for OS/DB/WEB(IIS/Apache) tiers to perform Trend Analysis.
These reports can be useful for troubleshooting the performance or capturing the baseline of the system.
In Troubleshooting approach, Understanding the problem and Measuring the change results are the keys to resolve the issue.


OS Trend Analysis:
As part of being able to monitor the performance effectively use OS utilities. Using correct utilities to find CPU, memory and disk I/O is crucial.
Side Note: We can also use Oracle OS Watcher(OSW), it depends on how easy to refer/compare the days worth of old data.

CPU:
sar -u

CREATE TABLE SYSTEM.OSMON_SARS
(
SAMPLE_DATE DATE,
SERVER_NAME VARCHAR2(50),
USER_CPU NUMBER,
SYSTEM_CPU NUMBER,
WAITIO_CPU NUMBER,
IDLE_CPU NUMBER
);


$ cat sar.sh

#!/bin/sh
############################################################
# File Name : sar.sh
# Author : Vijay Dumpa
# Usage : sar.sh ORACLE_SID
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for job
##
#set -x
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep -v "#" | grep $ORACLE_SID | cut -d ":" -f2`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`; export SERVER_NAME

sar -u > /tmp/msg$$

# Run sar and direct the output into the Oracle table . . .
cat /tmp/msg$$| tail -3 | head -1 | awk '{printf("%s %s %s %s\n",$2,$3,$4,$5)}' | while read USER_CPU SYSTEM_CPU WAITIO_CPU IDLE_CPU
do
$ORACLE_HOME/bin/sqlplus -s system/xxxxxx@$1 </dev/null
insert into system.osmon_sars(sample_date, server_name, user_cpu, system_cpu, waitio_cpu, idle_cpu)
values (SYSDATE, '$SERVER_NAME', $USER_CPU, $SYSTEM_CPU, $WAITIO_CPU, $IDLE_CPU);
commit;
exit;
EOF
done

rm /tmp/msg$$


Memory:
Swap: swap -s or vmstat


CREATE TABLE SYSTEM.OSMON_VMSTATS
(
SAMPLE_DATE DATE,
SAMPLE_TIME NUMBER,
SERVER_NAME VARCHAR2(50),
RUNQUE NUMBER,
FREE_SWAP NUMBER,
FREE_MEMORY NUMBER,
PAGE_IN NUMBER,
PAGE_OUT NUMBER
);


$ cat vmstat.sh

#!/bin/sh
############################################################
# File Name : vmstat.sh
# Author : Vijay Dumpa
# Usage : vmstat.sh ORACLE_SID
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for job
##
#set -x
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep -v "#" | grep $ORACLE_SID | cut -d ":" -f2`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`; export SERVER_NAME

# sample every 10 seconds.
SAMPLE_TIME=10
vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$

# Run vmstat and direct the output into the Oracle table . . .
#Solaris
cat /tmp/msg$$|sed 1,3d | awk '{printf("%s %s %s %s %s\n",$1,$8,$9,$4,$5)}' | while read RUNQUE PAGE_IN PAGE_OUT FREE_SWAP FREE_MEMORY
do
$ORACLE_HOME/bin/sqlplus -s system/xxxxxx@$1 </dev/null
insert into system.osmon_vmstats(sample_date, sample_time, server_name, runque, page_in, page_out, free_swap, free_memory)
values (SYSDATE, $SAMPLE_TIME, '$SERVER_NAME', $RUNQUE, $PAGE_IN, $PAGE_OUT, $FREE_SWAP, $FREE_MEMORY);
commit;
exit;
EOF
done

rm /tmp/msg$$


Disk I/O:
iostat -xnp.

CREATE TABLE SYSTEM.OSMON_IOSTATS
(
SAMPLE_DATE DATE,
SERVER_NAME VARCHAR2(50),
READS_PER_SEC NUMBER,
WRITES_PER_SEC NUMBER,
KB_READS_PER_SEC NUMBER,
KB_WRITES_PER_SEC NUMBER,
PERCENT_TRANS_WAIT NUMBER,
PERCENT_DISK_BUSY NUMBER,
FILE_SYSTEM_DEVICE VARCHAR2(50),
MOUNT_POINT VARCHAR2(10)
);


$ cat iostat.sh (Replace physic device names in below INSERT statement)

#!/bin/sh
############################################################
# File Name : iostat.sh
# Author : Vijay Dumpa
# Usage : iostat.sh ORACLE_SID
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for job
##
#set -x
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=`cat /var/opt/oracle/oratab | grep -v "#" | grep $ORACLE_SID | cut -d ":" -f2`; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
SERVER_NAME=`uname -a|awk '{print $2}'`; export SERVER_NAME

iostat -xnp > /tmp/msg_io
cat /tmp/msg_io | grep s6 > /tmp/msg$$

# Run iostat and direct the output into the Oracle table . . .
#Solaris
cat /tmp/msg$$| awk '{printf("%s %s %s %s %s %s %s\n",$1,$2,$3,$4,$9,$10,$11)}' | while read READS_PER_SEC WRITES_PER_SEC KB_READS_PER_SEC KB_WRITES_PER_SEC PERCENT_TRANS_WAIT PERCENT_DISK_BUSY FILE_SYSTEM_DEVICE
do
$ORACLE_HOME/bin/sqlplus -s system/xxxxxx@$1 </dev/null
insert into system.osmon_iostats (sample_date, server_name, reads_per_sec, writes_per_sec, kb_reads_per_sec, kb_writes_per_sec,
percent_trans_wait, percent_disk_busy, file_system_device, mount_point)
values (SYSDATE, '$SERVER_NAME', $READS_PER_SEC, $WRITES_PER_SEC, $KB_READS_PER_SEC, $KB_WRITES_PER_SEC,
$PERCENT_TRANS_WAIT, $PERCENT_DISK_BUSY, '$FILE_SYSTEM_DEVICE',
(case when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx1' then 'u01'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx2' then 'u02'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx3' then 'u03'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx4' then 'u04'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx5' then 'u05'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx6' then 'u06'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx7' then 'u07'
when '$FILE_SYSTEM_DEVICE' = 'xxxxxxxxxxxxxxxxxxxxxxxx8' then 'u08'
else 'new'
end)
);
commit;
exit;
EOF
done

rm /tmp/msg_io
rm /tmp/msg$$



DB Trend Analysis:
What STATSPACK, AWR Data is Best for Trend Analysis?
With hundreds of statistics available in STATSPACK, AWR the question arises as to what metrics are the most useful?

Sorts:
It is important to monitor the amount of sorting in the database, especially the amount of sorting that is done in the TEMP tablespace (sorts (disk)).

Physical disk reads:
The reduction of I/O is the primary goal of Oracle tuning, so a trend report showing hourly disk reads can be very useful for measuring the effect of table reorganizations the re-sequence rows into index order.

Physical disk writes:
The amount of disk writes has a great impact on the overall throughput of the Oracle database, and is especially important when rollback segment activity is critical.

I/O waits:
This is a very important metric that can be used to identify and correct I/O contention. High I/O waits occur when two or more data files are being accessed simultaneously and the read-write head movement of the DASD causes the disk to wait for the completion of I/O.

Buffer Busy Waits:
A buffer bust wait occurs when a Oracle data block resides in a buffer, but the requesting program must wait to retrieve the data block.
Buffer Busy Waits can commonly occur when a table has concurrent 'UPDATE' or 'INSERT DML' and only one freelist is defined for the table.

Redo log space requests:
Redo Log space requests occur when the log buffer is too small to hold the volume of redo log activity.

Latch pin hit ratio:
The pin hit ratios for the database instance give a good idea of the sizing of the shared_pool_size parameter in the init.ora file.

Table Fetch Continued Row:
This metric give a general indication of database migrated/chained rows. When chaining becomes excessive, additional I/O is required to service a query, and the DBA should increase PCTFREE for the table and reorganize the table to remove the chains.



Web Page Load Trend Analysis:
It's very importance to see, how the web pages are getting benefited by the particular DB change or any change(like Code, OS etc..).

Idea is to use IIS, Apache log file to generate webpage load report for that day.
Some thing like this:

Total Pages served : 22606

Pages 0 to 5 secs : 20041 (88.65%)
Pages 6 to 10 secs : 1515 (6.70%)
Pages 11 to 30 secs : xxxx (3%)
Pages more than 31 secs : xxxx (2.65%)

and Display TOP 10 pages URL's.


How to generate a web page load report from IIS log:

Step 1:
As part of this, you have to enable extended logging for cs-uri-stem and time-taken fields and also make sure the positions of the fields are 6 and 15 in IIS log file.

Step 2:
Perl Script: gen_IIS_webpage_load_report.pl (Schedule below script to run daily from Windows scheduler at 11:50pm after modifying email names, subject and log file location)

#!perl

use Time::localtime;
use Net::SMTP;
use Mail::Sender;


my $month = localtime->mon + 1;
#print "Month: $month\n";
my $day = localtime->mday;
#print "Day: $day\n";
my $year = localtime->year + 1900;
$year = sprintf("%02d", $year % 100);
my $hour = localtime->hour;
my $min = localtime->min;
my $second = localtime->sec;

if ($month < 10) {
$month = "0$month";
}
if ($day < 10) {
$day = "0$day";
# print "Day after 0: $day\n";
}
if ($hour < 10) {
$hour = "0$hour";
}
if ($min < 10) {
$min = "0$min";
}
if ($second < 10) {
$second = "0$second";
}

my $tmpDate = `date \/T`;
my $tmpTime = $hour . ":" . $min . ":" . $second;

my $dateNow = $tmpDate . $tmpTime;


my $time = $year . $month . $day;
#print "Time: $time\n";

my $alog = "c:\\perl_scripts\\windows\\" . "ex" . $time . ".log";
#print "Alog: $alog\n";

my $tmpFile0 = "c:\\perl_scripts\\windows\\tmplist0." . $time;
#print "TmpFile0: $tmpFile0\n";
my $tmpFile1 = "c:\\perl_scripts\\windows\\tmplist1." . $time;
#print "TmpFile1: $tmpFile1\n";
my $tmpFile2 = "c:\\perl_scripts\\windows\\tmplist2." . $time;
#print "TmpFile2: $tmpFile2\n";
my $tmpFile3 = "c:\\perl_scripts\\windows\\tmplist3." . $time;
#print "TmpFile3: $tmpFile3\n";
my $tmpFile4 = "c:\\perl_scripts\\windows\\tmplist4." . $time . ".html";
#print "TmpFile4: $tmpFile4\n";
my $tmpFile5 = "c:\\perl_scripts\\windows\\tmplist5." . $time;
#print "TmpFile5: $tmpFile5\n";
my $tmpFile6 = "c:\\perl_scripts\\windows\\tmplist6." . $time;
#print "TmpFile6: $tmpFile6\n";
my $tmpFile7 = "c:\\perl_scripts\\windows\\tmplist7." . $time;
#print "TmpFile6: $tmpFile7\n";

my $plusZero = 0;
my $plusFive = 0;
my $plusTen = 0;
my $plusFifteen = 0;
my $plusTwenty = 0;
my $plusTwentyFive = 0;
my $plusThirty = 0;
my $pageCnt = 0;
my $lineCnt = 0;

open (TMPFILE5, ">>$tmpFile5") or die "Cannot open $tmpFile5: $!";
open (TMPFILE0, ">$tmpFile0") or die "Cannot open $tmpFile0: $!";
open (LOG, "$alog") or die "Cannot open $alog: $!";
while () {
my $line = $_;
# print "Line from log: $line\n";
chomp $line;
$lineCnt++;
($date,$fileTime,$sitname,$computerName,$sip,$method,$uriStem,$query,$port,$username,$cip,$csVersion,$csUserAgent,$csCookie,$csReferer,$csHost,$scStatus,$subStatus,$win32Status,$scBytes,$csBytes,$timeTaken) = split(/\s+/, $line);
if ($lineCnt == 4) {
if ($line !~ /time-taken$/) {
sendMailMessage("message.xyzCompanyname.com", "firstname.lastname\@xyzCompanyname.com", "firstname.lastname\@xyzCompanyname.com", "$alog is not formatted as expected. Time taken field in $alog is blank.\n", "XYZsite Prod Report - Log not formatted correctly");
#system ("c:\\blat.exe \"$alog is not formatted as expected. Time taken field in $alog is blank.\n\" -server message.xyzCompanyname.com -f firstname.lastname\@xyzCompanyname.com -to firstname.lastname\@xyzCompanyname.com -s \"Log not formatted correctly\"");
exit 1;
}
}
#print "TimeTaken: $timeTaken\n";
if ($uriStem =~ m/.asp/) {
# print "$uriStem : $timeTaken\n";
print TMPFILE0 $uriStem . " " . $timeTaken . "\n";
}
$pageCnt++;
#print "PageCnt: $pageCnt\n";

if ($timeTaken < 500) {
$plusZero++;
# print "$plusZero\n";
} elsif ($timeTaken < 1000) {
$plusFive++;
# print "$plusFive\n";
} elsif ($timeTaken < 1500) {
$plusTen++;
# print "$plusTen\n";
} elsif ($timeTaken < 2000) {
$plusFifteen++;
# print "$plusFifteen\n";
} elsif ($timeTaken < 2500) {
$plusTwenty++;
# print "$plusTwenty\n";
} elsif ($timeTaken < 3000) {
$plusTwentyFive++;
# print "$plusTwentyFive\n";
} elsif ($timeTaken >= 3000) {
$plusThirty++;
#print "$plusThirty\n";
}
}

print TMPFILE5 "PAGECNT:$pageCnt\n";
print TMPFILE5 "PLUSZERO:$plusZero\n";
print TMPFILE5 "PLUSFIVE:$plusFive\n";
print TMPFILE5 "PLUSTEN:$plusTen\n";
print TMPFILE5 "PLUSFIFTEEN:$plusFifteen\n";
print TMPFILE5 "PLUSTWENTY:$plusTwenty\n";
print TMPFILE5 "PLUSTWENTYFIVE:$plusTwentyFive\n";
print TMPFILE5 "PLUSTHIRTY:$plusThirty\n";

close(LOG);
close(TMPFILE0);
close(TMPFILE5);

open (TMPFILE0, "$tmpFile0") or die "Cannot open $tmpFile0: $!";
#open (TMPFILE1, ">$tmpFile1") or die "Cannot open $tmpFile1: $!";
open (TMPFILE2, ">$tmpFile2") or die "Cannot open $tmpFile2: $!";

my @unique = ();
my @long = ();

while () {
my $line2 = $_;
chomp($line2);
($uriStem, $timeTaken) = split(/\s+/, $line2);

unless($i{$_}++) {
push(@long, $timeTaken);
}
}

foreach $time3 (@long) {
printf TMPFILE2 ("%09d", $time3);
print TMPFILE2 "\n";
}
close(TMPFILE2);

open (TMPFILE2, "$tmpFile2") or die "Cannot open $tmpFile2: $!";
my @long2 = ;
close(TMPFILE2);

@sorted = reverse sort @long2;
#print "sorted: @sorted\n";

my $count = 0;

open (TMPFILE6, ">$tmpFile6") or die "Cannot open $tmpFile6: $!";
foreach $sortedTime (@sorted) {
$count++;
if ($count <= 10) {
$sortedTime =~ s/^0*//;
print TMPFILE6 $sortedTime;
}
}
close(TMPFILE6);
close(TMPFILE0);
#close(TMPFILE1);
close(TMPFILE2);

open (TMPFILE6, "$tmpFile6") or die "Cannot open $tmpFile6: $!";
my @sortedTimes = ;
#print "SortedTimes: @sortedTimes\n";
close(TMPFILE6);

my @maxTime = ();
open (TMPFILE3, ">$tmpFile3") or die "Cannot open $tmpFile3: $!\n";
open (TMPFILE0, "$tmpFile0") or die "Cannot open $tmpFile0: $!";
my $match = "";

while () {
my $line2 = $_;
chomp($line2);
#print "Line2: $line\n";
foreach $num (@sortedTimes) {
if (/$num/) {
$match = $line2;
print TMPFILE3 "$match\n";
}
}
#($timeTaken) = split(/\s+/, $line2);
#push(@unique, $uriStem);

}

close(TMPFILE0);
#close(TMPFILE1);
close(TMPFILE2);
close(TMPFILE3);

my @temp = ();
my @sortTemp = ();
open (TMPFILE7, ">$tmpFile7") or die "Cannot open $tmpFile7: $!";
open (TMPFILE3, "$tmpFile3") or die "Cannot open $tmpFile3: $!";
while () {
my $line4 = $_;
chomp($line);
($uri, $ttaken) = split(/\s+/, $line4);
# print "$uri : $ttaken";
$ttaken2 = sprintf("%09d", $ttaken);
# print "T Taken: $ttaken2\n";
print TMPFILE7 "$ttaken2:$uri\n";
}
close(TMPFILE7);
close(TMPFILE3);

open (TMPFILE7, "$tmpFile7") or die "Cannot open $tmpFile7: $!";
@temp = ;
@sortTemp = reverse sort @temp;
#print "Sort Temp: @sortTemp\n";
close(TMPFILE7);



open (TMPFILE5, "$tmpFile5") or die "Cannot open $tmpFile5: $!";
while () {
my $line3 = $_;
chomp($line3);
($label, $field) = split (/:/, $line3);
if ($label eq "PAGECNT") {
$pageCnt = $field;
} elsif ($label eq "PLUSZERO") {
$plusZero = $field;
} elsif ($label eq "PLUSFIVE") {
$plusFive = $field;
} elsif ($label eq "PLUSTEN") {
$plusTen = $field;
} elsif ($label eq "PLUSFIFTEEN") {
$plusFifteen = $field;
} elsif ($label eq "PLUSTWENTY") {
$plusTwenty = $field;
} elsif ($label eq "PLUSTWENTYFIVE") {
$plusTwentyFive = $field;
} elsif ($label eq "PLUSTHIRTY") {
$plusThirty = $field;
}
}

close(TMPFILE5);

my $plusZeroCalc = ($plusZero/$pageCnt) * 100;
my $plusFiveCalc = ($plusFive/$pageCnt) * 100;
my $plusTenCalc = ($plusTen/$pageCnt) * 100;
my $plusFifteenCalc = ($plusFifteen/$pageCnt) * 100;
my $plusTwenyCalc = ($plusTwenty/$pageCnt) * 100;
my $plusTwentyFiveCalc = ($plusTwentyFive/$pageCnt) * 100;
my $plusThirtyCalc = ($plusThirty/$pageCnt) * 100;



open (TMPFILE4, ">>$tmpFile4") or die "Cannot open $tmpFile4: $!";
print TMPFILE4 "Content-Type: text/html; charset=\"US-ASCII\"";

print TMPFILE4 "< HTML>

Total Pages served :$pageCnt
";
print TMPFILE4 "
Pages 0 to 5 secs :$plusZero (";
printf TMPFILE4 ("%.2f",$plusZeroCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 6 to 10 secs :$plusFive (";
printf TMPFILE4 ("%.2f",$plusFiveCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 11 to 15 secs :$plusTen (";
printf TMPFILE4 ("%.2f",$plusTenCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 16 to 20 secs :$plusFifteen (";
printf TMPFILE4 ("%.2f",$plusFifteenCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages 21 to 25 secs :$plusTwenty (";
printf TMPFILE4 ("%.2f",$plusTwentyCalc);
print TMPFILE4 "\%\n";
print TMPFILE4 "
Pages 26 to 30 secs :$plusTwentyFive (";
printf TMPFILE4 ("%.2f",$plusTwentyFiveCalc);
print TMPFILE4 "\%)\n";
print TMPFILE4 "
Pages +31 secs :$plusThirty (";
printf TMPFILE4 ("%.2f",$plusThirtyCalc);
print TMPFILE4 "\%)\n";


print TMPFILE4 "

Top 10 Slow XYZsite Production Web Pages for $dateNow

";
foreach my $line5 (@sortTemp) {
($ttake, $url) = split(/:/, $line5);
my $tt = $ttake;
$tt =~ s/^0*//;
print TMPFILE4 "URL: $url\n";
print TMPFILE4 "
MilliSeconds: $tt\n

";
}
close(TMPFILE4);

$sender = new Mail::Sender {[from => 'firstname.lastname@xyzCompanyname.com'], [to => 'firstname.lastname@xyzCompanyname.com']};
(ref ($sender->MailFile( {from => 'firstname.lastname@xyzCompanyname.com', to => 'firstname.lastname@xyzCompanyname.com, firstname2.lastname2@xyzCompanyname.com', smtp=> 'message.xyzCompanyname.com', subject=>"XYZsite Prod Web Page Report - $tmpDate", msg => "XYZsite Prod Web Page Report - $tmpDate\n\n", file => "$tmpFile4"})) and print "Mail sent OK.") or die "$Mail::Sender::Error\n";

unlink "$tmpFile0";
unlink "$tmpFile1";
unlink "$tmpFile2";
unlink "$tmpFile3";
unlink "$tmpFile4";
unlink "$tmpFile5";
unlink "$tmpFile6";
unlink "$tmpFile7";

sub sendMailMessage {
my $serverName = shift @_;
my $fromAddress = shift @_;
my $toAddress = shift @_;
my $theMessage = shift @_;
my $theSubject = shift @_;
$smtp = Net::SMTP->new($serverName);
$smtp->mail($fromAddress);
$smtp->to($toAddress);
$smtp->data();
$smtp->datasend("To: $toAddress");
$smtp->datasend("\n");
$smtp->datasend("Subject: $theSubject \n");
$smtp->datasend("\n");
$smtp->datasend($theMessage);
$smtp->dataend();
$smtp->quit;
}


How to generate a web page load report from Apache log:

Step 1:
Add below like in the apache config's file.

LogFormat "%h %l %u %t \"%r\" %>s %b \"%{User-Agent}i\" %T"
Note the %T at the end. This is where those #'s come from. The "normal" apache config doesn't usually include that %T.

Step 2:
Shell Script: gen_apache_webpage_load_report.sh (Schedule below script to runs daily from cron at 11:50pm after modifying email names, subject and log file location)

$ cat gen_apache_webpage_load_report.sh

#!/bin/ksh
############################################################
# File Name : gen_apache_webpage_load_report.sh
# Author : Vijay Dumpa
# Usage : gen_apache_webpage_load_report.sh
#
# Modification History
# Who When What
# -------- -------- ----------------------------------------
#
#############################################################
##
# Set up environment for the job
##
#set -x
TMPFILE0=/tmp/tmplist0.$$
TMPFILE1=/tmp/tmplist1.$$
TMPFILE2=/tmp/tmplist2.$$
TMPFILE3=/tmp/tmplist3.$$
TMPFILE4=/tmp/tmplist4.$$

ALOG=/local/apache/logs/https/access_log.`date +%y%m%d`
MAILEES=firstname.lastname@xyzCompanyname.com

PLUSZERO=0
PLUSFIVE=0
PLUSTEN=0
PLUSFIFTEEN=0
PLUSTWENTY=0
PLUSTWENTYFIVE=0
PLUSTHIRTY=0
PAGECNT=0

# filter the original Apche access log to get the two required fields, and sort them by URL and then in numeric order per URL
egrep -v '.gif|.css|.js|.jpg|.htm|.bmp' $ALOG | awk '{print $NF, " ", $7}' | awk -F\? '{print $1}' | sort -k 2,2 -k 1,1rn > $TMPFILE0


exec 3<&0 < $TMPFILE0
while read -r INPUT
do
PAGECNT=`expr $PAGECNT + 1`
NUM=`echo $INPUT | awk '{print $1}'`

if [ $NUM -lt 6 ]; then
PLUSZERO=`expr $PLUSZERO + 1`
elif [ $NUM -lt 11 ]; then
PLUSFIVE=`expr $PLUSFIVE + 1`
elif [ $NUM -lt 16 ]; then
PLUSTEN=`expr $PLUSTEN + 1`
elif [ $NUM -lt 21 ]; then
PLUSFIFTEEN=`expr $PLUSFIFTEEN + 1`
elif [ $NUM -lt 26 ]; then
PLUSTWENTY=`expr $PLUSTWENTY + 1`
elif [ $NUM -lt 31 ]; then
PLUSTWENTYFIVE=`expr $PLUSTWENTYFIVE + 1`
elif [ $NUM -ge 31 ]; then
PLUSTHIRTY=`expr $PLUSTHIRTY + 1`
fi
done
exec 0<&3 3<&-

# filter out the highest value for each URL and make a new list of the top 25
uniq -f 1 $TMPFILE0 | sort -rn | head -25 > $TMPFILE1

# sort the main list of all URLs and all times in numeric order, highest first
sort -rn $TMPFILE0 > $TMPFILE2

# Get all of the timings for each page view, and then take the top 50 columns
exec 3<&0 < $TMPFILE1
while read -r INPUT
do
TMP1=`echo $INPUT | awk '{print $2}'`
TMP2=`grep " $TMP1" $TMPFILE2 | awk '{printf "%s, ",$1}'`
TMP3=`echo $TMP2 | cut -c 1-120 | awk -F, '{print $1" "$2" "$3" "$4" "$5" "$6" "$7" "$8" "$9" "$10" "$11" "$12" "$13" "$14" "$15" "$16" "$17" "$18" "$19" "$20" "$21" "$22" "$23" "$24" "$25}'`
echo $TMP3:$TMP1 >> $TMPFILE3
done
exec 0<&3 3<&-

echo "Subject:"XYZsite" Prod web page report - `date`" > $TMPFILE4
echo "Content-Type: text/html; charset=\"US-ASCII\"" >> $TMPFILE4

echo "< HTML>Total Pages served :$PAGECNT\n
" >> $TMPFILE4
echo "
Pages 0 to 5 secs :$PLUSZERO (`echo $PLUSZERO $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 6 to 10 secs :$PLUSFIVE (`echo $PLUSFIVE $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 11 to 15 secs :$PLUSTEN (`echo $PLUSTEN $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 16 to 20 secs :$PLUSFIFTEEN (`echo $PLUSFIFTEEN $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 21 to 25 secs :$PLUSTWENTY (`echo $PLUSTWENTY $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages 26 to 30 secs :$PLUSTWENTYFIVE (`echo $PLUSTWENTYFIVE $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)" >> $TMPFILE4
echo "
Pages +31 secs :$PLUSTHIRTY (`echo $PLUSTHIRTY $PAGECNT | awk '{printf "%.2f",(($1/$2)*100)}'`%)\n" >> $TMPFILE4


echo "

Top 10 Page times of the top 25 Slow "XYZsite" Production Web Pages for `date`

" >> $TMPFILE4

# re-sort the list with the slowest pages first
sort -rn $TMPFILE3 | awk -F: '{print "URL: "$2"\n
Seconds:
\n"$1"\n

"}' >> $TMPFILE4

cat $TMPFILE4 | /usr/lib/sendmail $MAILEES

rm $TMPFILE0 $TMPFILE1 $TMPFILE2 $TMPFILE3 $TMPFILE4

Friday, October 17, 2008

How to Shrink the datafiles in Oracle

1. Script to generate and run to set the maxbytes of the datafiles and shrink the datafiles:
SET echo off head off serveroutput off termout off feedback off verify off space 0;
SET linesize 150;
SET pagesize 0;

COLUMN value new_val blksize;
SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size'
/

SPOOL gen_setmaxbytes.sql
SELECT 'spool gen_setmaxbytes.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' autoextend on next 100m maxsize '
|| BYTES / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes = 0
UNION ALL
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| maxbytes / 1024 / 1024
|| 'M;'
FROM dba_data_files
WHERE maxbytes < BYTES AND maxbytes > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;


SPOOL gen_shrink_dbfiles.sql
SELECT 'spool gen_shrink_dbfiles.log'
FROM DUAL;
SELECT 'alter database datafile '''
|| file_name
|| ''' resize '
|| CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024)
|| 'm;' cmd
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b
WHERE a.file_id = b.file_id(+)
AND CEIL (blocks * &&blksize / 1024 / 1024)
- CEIL ((NVL (hwm, 1) * &&blksize) / 1024 / 1024) > 0
/
SELECT 'spool off;'
FROM DUAL;
SPOOL off;


SET echo on feedback on;

@gen_setmaxbytes.sql
@gen_shrink_dbfiles.sql

EXIT;


2. Script to generate rebuild objects at the end of the given datafile:
Note: Replace the file name with actual datafile name; which you are not able to shrink after reorganization, because some objects have extents at the end of the datafile. Check and change the below script for partition tables.

SELECT mst.altst
FROM (SELECT 'ALTER TABLE ' || owner || '.' || segment_name
|| ' MOVE;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'TABLE'
UNION
SELECT 'ALTER INDEX '
|| owner
|| '.'
|| segment_name
|| ' REBUILD;' altst,
block_id bid
FROM dba_extents
WHERE file_id = (SELECT file_id
FROM dba_data_files
WHERE file_name = '/u01/oradata/ORASID/ts01.dbf')
AND segment_type = 'INDEX'
ORDER BY bid DESC) mst;

Friday, October 3, 2008

Database and Server Inventory Management using Oracle Application Express, or APEX (Oracle HTML DB):

1. Download Oracle Application Express:
http://www.oracle.com/technology/software/products/database/xe/htdocs/102xelinsoft.html


2. Install rpm:
Login as root!!!

rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm

Output:
[root@dbasvr1 oraclexp]# rpm -ivh oracle-xe-univ-10.2.0.1-1.0.i386.rpm
Preparing... ########################################### [100%]
1:oracle-xe-univ ########################################### [100%]
Executing Post-install steps...

You must run '/etc/init.d/oracle-xe configure' as the root user to
configure the database.
[root@dbasvr1 oraclexp]#


3. Run /etc/init.d/oracle-xe configure as the root user:

Output:
[root@dbasvr1 oraclexp]# /etc/init.d/oracle-xe configure

Oracle Database 10g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 10g Express
Edition. The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts. Press to accept the defaults.
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Port 1521 appears to be in use by another application. Please specify a different port.
Specify a port that will be used for the database listener [1521]:1525

Specify a password to be used for database accounts. Note that the same
password will be used for SYS and SYSTEM. Oracle recommends the use of
different passwords for each database account. This can be done after
initial configuration: -- oracle
Confirm the password: -- oracle

Do you want Oracle Database 10g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring Database...Done
Starting Oracle Database 10g Express Edition Instance...Done
Installation Completed Successfully.
To access the Database Home Page go to "http://dbasvr1:8080/apex"
[root@dbasvr1 oraclexp]#

To start the database manually, run this command:
$ /etc/init.d/oracle-xe start

To stop the database manually, use the following command:
$ /etc/init.d/oracle-xe stop


4. Login and Create users:
Login as system user and goto Administration > Manage Database Users and create below users.

hp_dba/hp_dba
vdumpa/vdumpa
guest/guest


5. Create the database inventory table in hp_dba schema to store the information:

create sequence hp_dba.db_inv_seq start with 1 increment by 1;

create table hp_dba.db_inv(
db_no number(10) primary key,
Business_Unit varchar2(40),
db_name varchar2(20),
instance_name varchar2(20),
server_name varchar2(30),
Server_config varchar2(50),
DB_version varchar2(20),
OS_version varchar2(20),
DB_Env varchar2(30),
DB_size varchar2(20),
SGA_size varchar2(20),
PGA_size varchar2(20),
backup_type varchar2(40),
backup_frequencey varchar2(40),
physical_location varchar2(20),
verified_date date,
verified_by varchar2(40),
Primary_DBA varchar2(30),
Secondary_DBA varchar2(30),
Customer_name VARCHAR2(40),
DB_Control_URL varchar2(100),
Description varchar2(100))
tablespace users;

grant select on hp_dba.db_inv to guest;
grant select, insert, update, delete on hp_dba.db_inv to vdumpa;

CREATE OR REPLACE TRIGGER hp_dba.db_inv_BI
BEFORE INSERT
ON hp_dba.db_inv REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
begin
if :new.DB_NO is null then
select db_inv_seq.nextval
into :new.DB_NO
from dual;
end if;
end;


6. Login as hp_dba user to create Application:

a. On the Database Home Page, click the Application Builder icon.
b. Click the Create button.
c. Under Create Application, select Create Application and click Next.
d. Under Create Application:
Name: Enter "dbinv".
Accept the remaining defaults(Application(100)).
Click Next.
Next, add pages to your application.
e. Under Add Page:
For Select Page Type, select Report and Form
Next to the Table Name field, click the up arrow, and then select db_inv
from the Search Dialog window.
Click Add Page.
Click Next.
f. On the Tabs panel, select Two Level of Tabs and click Next.
g. On the Shared Components panel, accept the default (No) and click Next.
h. For Authentication Scheme, Language, and User Language Preference Derived From,
accept the defaults and click Next.
i. For the theme, select Theme 2 click Next.
j. After you click Create, the following message displays at the top of the page:
Application created successfully.


7. Running Your New Application:

a. To run your application, Click the Run Application icon.
b. In the log in page, enter hp_dba for both the User Name and Password
c. Search/Edit/Create the values from this page.


8. Public guest user access link:
http://dbasvr1:8080/apex/f?p=100:1


9. Backing Up the schema data:
EX: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs > cat /etc/oratab
XE:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server:N

EX: /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs > . oraenv
ORACLE_SID = [EX] ? XE

XE: /home/oracle > exp hp_dba/hp_dba file=exp_hp_dba.dmp

Export: Release 10.2.0.1.0 - Production on Fri Oct 17 09:00:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HP_DBA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HP_DBA
About to export HP_DBA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HP_DBA's tables via Conventional Path ...
. . exporting table DB_INV 37 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

Wednesday, July 2, 2008

RAC unique backup and restore challenges using OEM Grid Control in 10g:

How different is to backup and restore the RAC database VS. single instance database?

Consider the architectural nature of the Oracle RAC cluster:
In RAC setup, you have at least two different servers, oracle uses this hardware by creating two instances,
one on each node, with their own instance memory area(SGA).
Each instance has its own redo logs, but they exist on the shared disk and are accessible by all the nodes in the cluster.
All controlfiles and datafiles are shared between the two/more instances in the cluster.

RMAN perspective, this architecture creates interesting challenges for taking backups and restores, rman can connect to only a single node.
This shouldn't pose any problems for taking backup, but we do have a interesting challenge when it comes to restore.
In other words, we have to start the restore from the same node as backup was performed or need to use parms "ENV=(NB_ORA_CLIENT=atlt47)" in your allocate channel command.


CASE:
Scenario:
In one of our application, customer pre-requisite is to set NLS_DATE_FORMAT to 'RRRR-MON-DD' (default is 'DD-MON-RR').

Steps:
a.

Setting the NLS_DATE_FORMAT to 'RRRR-MON-DD' and restarting the RAC database:
SQL> alter system set NLS_DATE_FORMAT='RRRR-MON-DD' scope=spfile sid='*';
$ srvctl stop database -d DBName
$ srvctl start database -d DBName

b.
Resetting the NLS_DATE_FORMAT to default: <<<<< user set NLS_DATE_FORMAT to NULL, thinking resetting it to default value!!! and got oracle internal error.
SQL> alter system set NLS_DATE_FORMAT=NULL scope=spfile sid='*';

$ srvctl stop database -d DBName
$ srvctl start database -d DBName

ORA-00600: internal error code, arguments: [kccsbck_first], [1], [2792458534], [], [], [], [], []

c.
1.
So user decided to restore the DB to just before step a backup (Yes, user did take full rman backup!!!)

Important points to observe about the rman full backup:
Backup was taken from OEM GRID on cluster database level, instead of instance level.
In this case backup was taken from node 2(instance 2).
RMAN backup configured to VTL's (Via VERITAS NetBackup for Oracle - Release 5.1).

2.
User who scheduled a rman restore from OEM GRID, thinking that OEM GRID can automatically detect the backup performed node and restores it to point of time requested in the restore.

3.
What happened?

This time restore job connected to node 1(instance 1) and started the restore and recovery.

Did the restore successful?
Yes, but it restored the latest backup taken from the node 1(instance 1), which is 19 days old and it reset the logs to open the database.


RMAN error log and restoring 19 days old backup from OEM GRID:

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> SQL> Connected to an idle instance.
SQL> SQL> ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size 2072224 bytes
Variable Size 285213024 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes
Database mounted.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 29 16:26:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN>
connected to target database: ORADB (DBID=390676631, not open)

RMAN>
connected to recovery catalog database

RMAN>
echo set on


RMAN> run {
2> restore database;
3> recover database noredo;
4> }
Starting restore at 29-JUN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1077 instance=ORADB1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1076 instance=ORADB1 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=1075 instance=ORADB1 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=1074 instance=ORADB1 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1073 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=1072 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: sid=1071 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_3: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: sid=1070 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_4: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_5
channel ORA_SBT_TAPE_5: sid=1069 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_5: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_6
channel ORA_SBT_TAPE_6: sid=1068 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_6: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_7
channel ORA_SBT_TAPE_7: sid=1067 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_7: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)
allocated channel: ORA_SBT_TAPE_8
channel ORA_SBT_TAPE_8: sid=1066 instance=ORADB1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_8: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

channel ORA_SBT_TAPE_2: starting datafile backupset restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel ORA_SBT_TAPE_2: reading from backup piece 0qjk51um_1_1
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora
channel ORA_SBT_TAPE_1: reading from backup piece 0sjk51un_1_1
channel ORA_SBT_TAPE_3: starting datafile backupset restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel ORA_SBT_TAPE_3: reading from backup piece 0pjk51um_1_1
channel ORA_SBT_TAPE_4: starting datafile backupset restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel ORA_SBT_TAPE_4: reading from backup piece 0rjk51un_1_1
channel ORA_SBT_TAPE_5: starting datafile backupset restore
channel ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel ORA_SBT_TAPE_5: reading from backup piece 0ojk51um_1_1
channel ORA_SBT_TAPE_6: starting datafile backupset restore
channel ORA_SBT_TAPE_6: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel ORA_SBT_TAPE_6: reading from backup piece 0njk51um_1_1
ORA-19870: error reading backup piece 0sjk51un_1_1
ORA-19507: failed to retrieve sequential file, handle="0sjk51un_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0sjk51un_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0qjk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0qjk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0qjk51um_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0pjk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0pjk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0pjk51um_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0rjk51un_1_1
ORA-19507: failed to retrieve sequential file, handle="0rjk51un_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0rjk51un_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0ojk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0ojk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0ojk51um_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0njk51um_1_1
ORA-19507: failed to retrieve sequential file, handle="0njk51um_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0njk51um_1_1> not found in NetBackup catalog
failover to previous backup

channel ORA_SBT_TAPE_3: starting datafile backupset restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel ORA_SBT_TAPE_3: reading from backup piece 0gjk2tut_1_1
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel ORA_SBT_TAPE_1: reading from backup piece 0jjk2tuu_1_1
channel ORA_SBT_TAPE_2: starting datafile backupset restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel ORA_SBT_TAPE_2: reading from backup piece 0ijk2tuu_1_1
channel ORA_SBT_TAPE_4: starting datafile backupset restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora
channel ORA_SBT_TAPE_4: reading from backup piece 0kjk2tuu_1_1
channel ORA_SBT_TAPE_5: starting datafile backupset restore
channel ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel ORA_SBT_TAPE_5: reading from backup piece 0hjk2tuu_1_1
channel ORA_SBT_TAPE_6: starting datafile backupset restore
channel ORA_SBT_TAPE_6: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel ORA_SBT_TAPE_6: reading from backup piece 0fjk2tut_1_1
ORA-19870: error reading backup piece 0jjk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0jjk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0jjk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0ijk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0ijk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0ijk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0gjk2tut_1_1
ORA-19507: failed to retrieve sequential file, handle="0gjk2tut_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0gjk2tut_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0kjk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0kjk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0kjk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0hjk2tuu_1_1
ORA-19507: failed to retrieve sequential file, handle="0hjk2tuu_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0hjk2tuu_1_1> not found in NetBackup catalog
ORA-19870: error reading backup piece 0fjk2tut_1_1
ORA-19507: failed to retrieve sequential file, handle="0fjk2tut_1_1", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Backup file <0fjk2tut_1_1> not found in NetBackup catalog
failover to previous backup

the filename for datafile 6 is missing in the control file
channel ORA_SBT_TAPE_4: starting datafile backupset restore
channel ORA_SBT_TAPE_4: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel ORA_SBT_TAPE_4: reading from backup piece 09jjvff1_1_1
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel ORA_SBT_TAPE_1: reading from backup piece 0djjvff2_1_1
channel ORA_SBT_TAPE_2: starting datafile backupset restore
channel ORA_SBT_TAPE_2: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel ORA_SBT_TAPE_2: reading from backup piece 0bjjvff1_1_1
channel ORA_SBT_TAPE_3: starting datafile backupset restore
channel ORA_SBT_TAPE_3: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel ORA_SBT_TAPE_3: reading from backup piece 0ajjvff1_1_1
channel ORA_SBT_TAPE_5: starting datafile backupset restore
channel ORA_SBT_TAPE_5: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel ORA_SBT_TAPE_5: reading from backup piece 08jjvff1_1_1
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=0djjvff2_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:03:25
channel ORA_SBT_TAPE_2: restored backup piece 1
piece handle=0bjjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_2: restore complete, elapsed time: 00:03:40
channel ORA_SBT_TAPE_3: restored backup piece 1
piece handle=0ajjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_3: restore complete, elapsed time: 00:03:40
channel ORA_SBT_TAPE_4: restored backup piece 1
piece handle=09jjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_4: restore complete, elapsed time: 00:03:40
channel ORA_SBT_TAPE_5: restored backup piece 1
piece handle=08jjvff1_1_1 tag=BACKUP_ORADB.WO_062708093125
channel ORA_SBT_TAPE_5: restore complete, elapsed time: 00:03:40
Finished restore at 29-JUN-08

Starting recover at 29-JUN-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
using channel ORA_SBT_TAPE_6
using channel ORA_SBT_TAPE_7
using channel ORA_SBT_TAPE_8
temporary file +INF_T1_BACKUP_01/qroa1inf/tempfile/temp.263.657026087 size altered in control file
Finished recover at 29-JUN-08


RMAN> exit;

Recovery Manager complete.

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 29 16:31:36 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN>
connected to target database: ORADB (DBID=390676631, not open)

RMAN>
connected to recovery catalog database

RMAN>
echo set on


RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


RMAN> exit;

Recovery Manager complete.


Action plan:
Connect to rman catalog (very important, default is NOCATALOG) and
reset the database incarnation Key to PARENT(before resetlogs) from the node 2(rman backup performed node).
Restore controlfile and database and recover the database to open.


RMAN restore log:
atlt47 | ORADB2 | /u00/app/oracle/product/10.2.0/DB
> rman TARGET / CATALOG rman/rman@prman.world

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 12:20:24 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORADB (not mounted)
connected to recovery catalog database

RMAN> shutdown immediate;

set DBID 390676631;

startup nomount;

Oracle instance shut down

RMAN>
RMAN>
executing command: SET DBID
database name is "ORADB" and DBID is 390676631

RMAN>
RMAN>
connected to target database (not started)
Oracle instance started

Total System Global Area 1157627904 bytes

Fixed Size 2072224 bytes
Variable Size 285213024 bytes
Database Buffers 855638016 bytes
Redo Buffers 14704640 bytes

RMAN> LIST INCARNATION OF DATABASE ORADB;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
4710871 4710872 ORADB 390676631 PARENT 1 10-JUN-08
4710871 4931854 ORADB 390676631 CURRENT 1971243 29-JUN-08

RMAN> list incarnation;


List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
4710871 4710872 ORADB 390676631 PARENT 1 10-JUN-08
4710871 4931854 ORADB 390676631 CURRENT 1971243 29-JUN-08

-------------------------------------------- <<<<<<<< giving the wrong(CURRENT) database incarnation Key.
RMAN> reset database to incarnation 4931854;

database reset to incarnation 4931854

RMAN> run {
2> set until time "to_date('JUN 29 2008 12:21:00','Mon DD YYYY HH24:MI:SS')";
3> allocate channel d1 type 'SBT_TAPE';
4> allocate channel d2 type 'SBT_TAPE';
5> allocate channel d3 type 'SBT_TAPE';
6> allocate channel d4 type 'SBT_TAPE';
7> restore controlfile;
8> sql "alter database mount";
9> restore database;
10> recover database;
11> sql "alter database open resetlogs";
12> release channel d1;
13> release channel d2;
14> release channel d3;
15> release channel d4;
16> }

executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 06/30/2008 13:29:08
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
--------------------------------------------

RMAN> reset database to incarnation 4710872; <<<<<<<< giving the correct(PARENT) database incarnation Key.
database reset to incarnation 4710872

RMAN> run {
2> set until time "to_date('JUN 29 2008 12:21:00','Mon DD YYYY HH24:MI:SS')";
3> allocate channel d1 type 'SBT_TAPE';
4> allocate channel d2 type 'SBT_TAPE';
5> allocate channel d3 type 'SBT_TAPE';
6> allocate channel d4 type 'SBT_TAPE';
7> restore controlfile;
8> sql "alter database mount";
9> restore database;
10> recover database;
11> sql "alter database open resetlogs";
12> release channel d1;
13> release channel d2;
14> release channel d3;
15> release channel d4;
16> }

executing command: SET until clause

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
released channel: ORA_SBT_TAPE_3
released channel: ORA_SBT_TAPE_4
released channel: ORA_SBT_TAPE_5
released channel: ORA_SBT_TAPE_6
released channel: ORA_SBT_TAPE_7
released channel: ORA_SBT_TAPE_8
allocated channel: d1
channel d1: sid=1079 instance=ORADB2 devtype=SBT_TAPE
channel d1: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: d2
channel d2: sid=1080 instance=ORADB2 devtype=SBT_TAPE
channel d2: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: d3
channel d3: sid=1078 instance=ORADB2 devtype=SBT_TAPE
channel d3: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

allocated channel: d4
channel d4: sid=1077 instance=ORADB2 devtype=SBT_TAPE
channel d4: VERITAS NetBackup for Oracle - Release 5.1 (2006040520)

Starting restore at 30-JUN-08

channel d1: starting datafile backupset restore
channel d1: restoring control file
channel d1: reading from backup piece 0tjk51un_1_1

channel d1: restored backup piece 1
piece handle=0tjk51un_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:35
output filename=+INF_T1_DATA_01/qroa1inf/controlfile/current.290.658762389
Finished restore at 30-JUN-08

sql statement: alter database mount

Starting restore at 30-JUN-08

channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093
channel d2: reading from backup piece 0qjk51um_1_1
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora
channel d1: reading from backup piece 0sjk51un_1_1
channel d1: restored backup piece 1
piece handle=0sjk51un_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:25
channel d2: restored backup piece 1
piece handle=0qjk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d2: restore complete, elapsed time: 00:03:25
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091
channel d1: reading from backup piece 0rjk51un_1_1
channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085
channel d2: reading from backup piece 0pjk51um_1_1
channel d1: restored backup piece 1
piece handle=0rjk51un_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:25
channel d2: restored backup piece 1
piece handle=0pjk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d2: restore complete, elapsed time: 00:03:25
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087
channel d1: reading from backup piece 0ojk51um_1_1
channel d1: restored backup piece 1
piece handle=0ojk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:35
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077
channel d1: reading from backup piece 0njk51um_1_1
channel d1: restored backup piece 1
piece handle=0njk51um_1_1 tag=BACKUP_ORADB.WO_062908121740
channel d1: restore complete, elapsed time: 00:03:35
Finished restore at 30-JUN-08

Starting recover at 30-JUN-08

starting media recovery

released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/30/2008 13:47:58
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 2 seq 13 lowscn 2607439 found to restore
RMAN-06025: no backup of log thread 1 seq 26 lowscn 2723046 found to restore

RMAN>
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 06/30/2008 13:49:38
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open RESETLOGS;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit


Recovery Manager complete.


Side Note:
To avoid manual "alter database open RESETLOGS;" use below rman commands.

recover database noredo; -- For NOARCHIVE LOG
recover database; -- For ARCHIVE LOG



P.S.
So, how is my list backupset command looks with NOCATALOG and CATALOG?

atlt47 | ORADB2 | /u00/app/oracle/product/10.2.0/DB> rman target / <<<<<<<<<< NOCATALOG
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 11:01:30 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORADB (DBID=390676631, not open)

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 256.00K SBT_TAPE 00:01:28 13-JUN-08
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 06jircsb_1_1 Media:
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 256.00K SBT_TAPE 00:01:36 13-JUN-08
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 07jircsb_1_1 Media:
SPFILE Included: Modification time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 14.75M SBT_TAPE 00:01:37 13-JUN-08
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 05jircsb_1_1 Media:
Control File Included: Ckp SCN: 862074 Ckp time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 5.00M SBT_TAPE 00:01:43 13-JUN-08
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 04jircsa_1_1 Media:
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 210.25M SBT_TAPE 00:01:55 13-JUN-08
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 03jircsa_1_1 Media:
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 333.25M SBT_TAPE 00:01:56 13-JUN-08
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 02jircsa_1_1 Media:
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 356.50M SBT_TAPE 00:01:58 13-JUN-08
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 01jircsa_1_1 Media:
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

RMAN>

RMAN> exit

Recovery Manager complete.



atlt47 | ORADB2 | /u00/app/oracle/product/10.2.0/DB> rman target / <<<<<<<<<< CATALOG
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 30 11:07:03 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ORADB (DBID=390676631, not open)

RMAN> connect catalog rman/rman@prman.world <<<<<<<<<< CATALOG

connected to recovery catalog database

RMAN> list backup;


List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728914 Full 256.00K SBT_TAPE 00:01:28 13-JUN-08
BP Key: 4728921 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 06jircsb_1_1 Media:
List of Datafiles in backup set 4728914
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728915 Full 256.00K SBT_TAPE 00:01:36 13-JUN-08
BP Key: 4728922 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 07jircsb_1_1 Media:
SPFILE Included: Modification time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728916 Full 14.75M SBT_TAPE 00:01:37 13-JUN-08
BP Key: 4728923 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 05jircsb_1_1 Media:
Control File Included: Ckp SCN: 862074 Ckp time: 13-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728917 Full 5.00M SBT_TAPE 00:01:43 13-JUN-08
BP Key: 4728924 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 04jircsa_1_1 Media:
List of Datafiles in backup set 4728917
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728918 Full 210.25M SBT_TAPE 00:01:55 13-JUN-08
BP Key: 4728925 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 03jircsa_1_1 Media:
List of Datafiles in backup set 4728918
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728919 Full 333.25M SBT_TAPE 00:01:56 13-JUN-08
BP Key: 4728926 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 02jircsa_1_1 Media:
List of Datafiles in backup set 4728919
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4728920 Full 356.50M SBT_TAPE 00:01:58 13-JUN-08
BP Key: 4728927 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_061308050625
Handle: 01jircsa_1_1 Media:
List of Datafiles in backup set 4728920
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 862074 13-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904245 Full 256.00K SBT_TAPE 00:01:17 27-JUN-08
BP Key: 4904253 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0djjvff2_1_1 Media:
List of Datafiles in backup set 4904245
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904246 Full 14.75M SBT_TAPE 00:01:21 27-JUN-08
BP Key: 4904254 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0cjjvff1_1_1 Media:
Control File Included: Ckp SCN: 1971242 Ckp time: 27-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904247 Full 256.00K SBT_TAPE 00:01:26 27-JUN-08
BP Key: 4904255 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0ejjvff2_1_1 Media:
SPFILE Included: Modification time: 27-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904248 Full 21.25M SBT_TAPE 00:01:31 27-JUN-08
BP Key: 4904256 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0bjjvff1_1_1 Media:
List of Datafiles in backup set 4904248
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904249 Full 333.25M SBT_TAPE 00:01:45 27-JUN-08
BP Key: 4904257 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 0ajjvff1_1_1 Media:
List of Datafiles in backup set 4904249
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904250 Full 344.00M SBT_TAPE 00:01:47 27-JUN-08
BP Key: 4904258 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 09jjvff1_1_1 Media:
List of Datafiles in backup set 4904250
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4904251 Full 362.00M SBT_TAPE 00:01:47 27-JUN-08
BP Key: 4904259 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062708093125
Handle: 08jjvff1_1_1 Media:
List of Datafiles in backup set 4904251
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1971242 27-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920736 Full 14.75M SBT_TAPE 00:01:32 28-JUN-08
BP Key: 4920745 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0ljk2tuu_1_1 Media:
Control File Included: Ckp SCN: 2589364 Ckp time: 28-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920737 Full 256.00K SBT_TAPE 00:01:42 28-JUN-08
BP Key: 4920746 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0mjk2tuu_1_1 Media:
SPFILE Included: Modification time: 28-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920738 Full 155.25M SBT_TAPE 00:01:46 28-JUN-08
BP Key: 4920747 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0jjk2tuu_1_1 Media:
List of Datafiles in backup set 4920738
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920739 Full 124.50M SBT_TAPE 00:01:52 28-JUN-08
BP Key: 4920748 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0ijk2tuu_1_1 Media:
List of Datafiles in backup set 4920739
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920740 Full 347.50M SBT_TAPE 00:01:55 28-JUN-08
BP Key: 4920749 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0gjk2tut_1_1 Media:
List of Datafiles in backup set 4920740
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920741 Full 256.00K SBT_TAPE 00:01:56 28-JUN-08
BP Key: 4920750 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0kjk2tuu_1_1 Media:
List of Datafiles in backup set 4920741
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920742 Full 333.25M SBT_TAPE 00:01:58 28-JUN-08
BP Key: 4920751 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0hjk2tuu_1_1 Media:
List of Datafiles in backup set 4920742
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4920743 Full 471.00M SBT_TAPE 00:02:02 28-JUN-08
BP Key: 4920752 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062808045704
Handle: 0fjk2tut_1_1 Media:
List of Datafiles in backup set 4920743
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2589364 28-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929973 Full 14.75M SBT_TAPE 00:01:23 29-JUN-08
BP Key: 4929982 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0tjk51un_1_1 Media:
Control File Included: Ckp SCN: 2780259 Ckp time: 29-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929974 Full 256.00K SBT_TAPE 00:01:31 29-JUN-08
BP Key: 4929983 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0ujk51un_1_1 Media:
SPFILE Included: Modification time: 29-JUN-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929975 Full 256.00K SBT_TAPE 00:01:32 29-JUN-08
BP Key: 4929984 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0sjk51un_1_1 Media:
List of Datafiles in backup set 4929975
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/infinys_data1.ora

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929976 Full 137.00M SBT_TAPE 00:01:40 29-JUN-08
BP Key: 4929985 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0qjk51um_1_1 Media:
List of Datafiles in backup set 4929976
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/users.265.657026093

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929977 Full 333.25M SBT_TAPE 00:01:41 29-JUN-08
BP Key: 4929986 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0pjk51um_1_1 Media:
List of Datafiles in backup set 4929977
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs1.261.657026085

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929978 Full 155.25M SBT_TAPE 00:01:42 29-JUN-08
BP Key: 4929987 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0rjk51un_1_1 Media:
List of Datafiles in backup set 4929978
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/undotbs2.264.657026091

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929979 Full 348.25M SBT_TAPE 00:01:49 29-JUN-08
BP Key: 4929988 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0ojk51um_1_1 Media:
List of Datafiles in backup set 4929979
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/sysaux.262.657026087

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4929980 Full 471.25M SBT_TAPE 00:01:52 29-JUN-08
BP Key: 4929989 Status: AVAILABLE Compressed: NO Tag: BACKUP_ORADB.WO_062908121740
Handle: 0njk51um_1_1 Media:
List of Datafiles in backup set 4929980
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2780259 29-JUN-08 +INF_T1_BACKUP_01/qroa1inf/datafile/system.260.657026077

RMAN>

RMAN> exit

Recovery Manager complete.

Saturday, May 31, 2008

ASM could not mount diskgroup or see the disks:

What might have caused it? What are my options to fix or recover?

What might have caused it?:
a.
asm_diskstring and/or asm_diskgroups init parameters are not set correctly

b.
OS Disks/Devices ownership

c.
OS Disks/Devices permissions

d.
Check if Physical Disks/Devices exist or visible at OS level

e.
Make sure oracle ASMLib rpm's are installed. - Linux

f.
Make sure all the disks can be listed

g.
Make sure all the disks can be queried

h.
Make sure permission and ownership of /var/opt/oracle directory.

i.
Make sure that there are no duplicate paths or names that point to the same physical disk

Specific to RAC:
j.
Make sure disk name and underlying logical device id or LUN id is consistent across all the nodes

k.
Make sure /proc/partitions file is consistent (no missing entries) across the nodes


What are my options to fix or recover?: Let's talk about all the options mentioned above.
a.
asm_diskstring and/or asm_diskgroups init parameters are not set correctly

Raw device:
ALTER SYSTEM SET asm_diskstring = '/dev/raw/raw*, /dev/raw/raw1[1-5]' scope=BOTH sid='*';

ASMLib:
ALTER SYSTEM SET asm_diskstring = 'ORCL:*' scope=BOTH sid='*';


ALTER SYSTEM SET asm_diskgroups = 'ORADB_DATA, ORADB_IDX, ORADB_BACKUP, ORADB_ARC' scope=spfile sid='*';


b.
OS Disks/Devices ownership

oracle:dba for non OCR and Voting Disk.

root:dba for OCR.
oracle:dba for Voting Disk.


c.
OS Disks/Devices permissions

660 - for non OCR and Voting Disk.

640 - for OCR.
644 - for Voting Disk.


d.
Check if Physical Disks/Devices exist or visible at OS level

Raw device:
/dev/raw

ASMLib:
/dev/oracleasm/disks

Side Note: For more information, please refer to my other note on "ASM SAN migration Case Study: - How to find mapping of ASM disks to Physical Devices?"


e.
Make sure oracle ASMLib rpm's are installed. - Linux

ASMLib only!!

Check:
rpm -qa|grep asm
oracleasmlib-2.0.2-1
oracleasm-support-2.0.3-1
oracleasm-2.6.9-42.ELsmp-2.0.3-1


f.
Make sure all the disks can be listed

/etc/init.d/oracleasm listdisks


g.
Make sure all the disks can be queried

List all the disks:
for i in `cd /dev/oracleasm/disks;ls *`;
do
/etc/init.d/oracleasm querydisk $i 2>/dev/null
done

Look for any missing label's:
for i in `ls /dev/emcpower*1`;
do
/etc/init.d/oracleasm querydisk $i 2>/dev/null | grep "\"\"$"
done


$/dev/oracleasm/disks> ls -lt AO_6011_0972
brw-rw---- 1 oracle dba 120, 641 Mar 21 17:03 AO_6011_0972

$/dev/oracleasm/disks> /etc/init.d/oracleasm querydisk AO_6011_0972
Disk "AO_6011_0972" is a valid ASM disk on device [120, 641]


Side Note 1: When you create ASM disks, its best practice to follow some kind of naming convention.
Why?

For example, easy to recreate missing labels or easy to identify physical storage.

CLARiiON:
$ ls -lt /dev/emcpowerem
brwxrwx--- 1 oracle dba 120, 2272 Mar 18 10:04 /dev/emcpowerem
$ ls -lt /dev/emcpowerem1
brwxrwx--- 1 oracle dba 120, 2273 Apr 24 17:32 /dev/emcpowerem1
$ ls -lt /dev/oracleasm/disks/EM*
brw-rw---- 1 oracle dba 120, 2273 Mar 18 10:46 /dev/oracleasm/disks/EM_5438_1227

# /sbin/powermt display dev=emcpowerem
Pseudo name=emcpowerem
CLARiiON ID=APM00043005438 [d03_ORADB_Dev]
Logical device ID=60060160272013007325DC0E5FF4DC11 [LUN 1227]
state=alive; policy=CLAROpt; priority=0; queued-IOs=0
Owner: default=SP B, current=SP B
==============================================================================
---------------- Host --------------- - Stor - -- I/O Path - -- Stats ---
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
1 lpfc sdabf SP B1 active alive 0 0
1 lpfc sdacp SP A1 active alive 0 0
1 lpfc sdadz SP B0 active alive 0 0
1 lpfc sdafj SP A0 active alive 0 0
2 lpfc sdagt SP B2 active alive 0 0
2 lpfc sdaid SP A2 active alive 0 0
2 lpfc sdajn SP B3 active alive 0 0
2 lpfc sdakx SP A3 active alive 0 0

Naming Standards:
For CLARiiON: [emcpower Id]_[CLARiiON Id(last 4 dig)]_[LUN Id]
For EMC DMX : [emcpower Id]_[Symmetrix Id(last 4 dig)]_[Logical device Id]



EMC DMX:
$ /dev> ls -lt emcpoweray
brwxrwx--- 1 oracle dba 120, 800 Apr 22 05:46 emcpoweray
$ /dev> /etc/init.d/oracleasm listdisks | grep AY
AY_0469_0BB9
$ /dev> /sbin/powermt display dev=emcpoweray
Pseudo name=emcpoweray
Symmetrix ID=000187880469
Logical device ID=0BB9
state=alive; policy=SymmOpt; priority=0; queued-IOs=0
==============================================================================
---------------- Host --------------- - Stor - -- I/O Path - -- Stats ---
### HW Path I/O Paths Interf. Mode State Q-IOs Errors
==============================================================================
1 lpfc sdbg FA 3cA active alive 0 0
2 lpfc sddv FA 14cA active alive 0 0


Side Note 2: Always take backup of all ASMLib disk headers.

for i in `cd /dev/oracleasm/disks/;ls *`;
do
dd if=/dev/oracleasm/disks/$i of=/tmp/$i.dump bs=4096 count=1
done


h.
Make sure permission and ownership of /var/opt/oracle directory.

oracle:dba and relax the permissions


i.
Make sure that there are no duplicate paths or names that point to the same physical disk
Such duplication result in the below error in alert log file.

ORA-15020: discovered duplicate ASM disk ""

We can detect this using this command: "/sbin/powermt display dev=emcpowerem"


j.
Make sure disk name and underlying logical device id or LUN id is consistent across the nodes

Some times, disk name pointing to logical device id or LUN id on one node is different from other nodes.

We can detect this using this command: "/sbin/powermt display dev=emcpowerem"
We can correct this using this command: "emcpadm rename -s emcpoweram -t emcpowerah"


k.
Make sure /proc/partitions file is consistent(no missing entries) across the nodes


Case 1:
ASMLib could not see SAN storage after reboot or missing ASMLib labels?:


How to create the kfed utility?:
from DB home:
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ikfed


We can recreate missing labels with below command:
# /etc/init.d/oracleasm force-renamedisk /dev/emcpoweray1 AY_0469_0BB9

Side Note: We can also look at ASM labels using kfed tool, kfed tool might have been used to tweak the headers.


kfed help in 10.2.0.3:
$ kfed help=y
as/mlib ASM Library [asmlib='lib']
aun/um AU number to examine or update [AUNUM=number]
aus/z Allocation Unit size in bytes [AUSZ=number]
blkn/um Block number to examine or update [BLKNUM=number]
blks/z Metadata block size in bytes [BLKSZ=number]
ch/ksum Update checksum before each write [CHKSUM=YES/NO]
cn/t Count of AUs to process [CNT=number]
d/ev ASM device to examine or update [DEV=string]
o/p KFED operation type [OP=READ/WRITE/MERGE/NEW/FORM/FIND/STRUCT]
p/rovnm Name for provisioning purposes [PROVNM=string]
te/xt File name for translated block text [TEXT=string]
ty/pe ASM metadata block type number [TYPE=number]
KFED-01000: USAGE: kfed [] [] []


e.g.:
$ ORACLE_ASM_HOME/bin/kfed read /dev/oracleasm/disks/AY_0469_0BB9
kfbh.endian: 1 ; 0x000: 0x01
kfbh.hard: 130 ; 0x001: 0x82
kfbh.type: 1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt: 1 ; 0x003: 0x01
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 2147483650 ; 0x008: TYPE=0x8 NUMB=0x2
kfbh.check: 2426726764 ; 0x00c: 0x90a4e96c
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:ORCLDISKAY_0469_0BB9 ; 0x000: length=20
kfdhdb.driver.reserved[0]: 811555137 ; 0x008: 0x305f5941
kfdhdb.driver.reserved[1]: 1597584948 ; 0x00c: 0x5f393634
kfdhdb.driver.reserved[2]: 960643632 ; 0x010: 0x39424230
kfdhdb.driver.reserved[3]: 0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]: 0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]: 0 ; 0x01c: 0x00000000
kfdhdb.compat: 168820736 ; 0x020: 0x0a100000
kfdhdb.dsknum: 2 ; 0x024: 0x0002
kfdhdb.grptyp: 1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts: 3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname: AY_0469_0BB9 ; 0x028: length=12
kfdhdb.grpname: ORADB_T1_BACKUP_01 ; 0x048: length=18

kfdhdb.fgname: AY_0469_0BB9 ; 0x068: length=12
kfdhdb.capname: ; 0x088: length=0
kfdhdb.crestmp.hi: 32902473 ; 0x0a8: HOUR=0x9 DAYS=0xa MNTH=0x3 YEAR=0x7d8
kfdhdb.crestmp.lo: 1396390912 ; 0x0ac: USEC=0x0 MSEC=0x2cf SECS=0x33 MINS=0x14
kfdhdb.mntstmp.hi: 32904078 ; 0x0b0: HOUR=0xe DAYS=0x1c MNTH=0x4 YEAR=0x7d8
kfdhdb.mntstmp.lo: 1783004160 ; 0x0b4: USEC=0x0 MSEC=0x19f SECS=0x24 MINS=0x1a
kfdhdb.secsize: 512 ; 0x0b8: 0x0200
kfdhdb.blksize: 4096 ; 0x0ba: 0x1000
kfdhdb.ausize: 1048576 ; 0x0bc: 0x00100000
kfdhdb.mfact: 113792 ; 0x0c0: 0x0001bc80
kfdhdb.dsksize: 17263 ; 0x0c4: 0x0000436f
kfdhdb.pmcnt: 2 ; 0x0c8: 0x00000002
kfdhdb.fstlocn: 1 ; 0x0cc: 0x00000001
kfdhdb.altlocn: 2 ; 0x0d0: 0x00000002
kfdhdb.f1b1locn: 0 ; 0x0d4: 0x00000000
kfdhdb.redomirrors[0]: 0 ; 0x0d8: 0x0000
kfdhdb.redomirrors[1]: 0 ; 0x0da: 0x0000
kfdhdb.redomirrors[2]: 0 ; 0x0dc: 0x0000
kfdhdb.redomirrors[3]: 0 ; 0x0de: 0x0000
kfdhdb.dbcompat: 168820736 ; 0x0e0: 0x0a100000
kfdhdb.grpstmp.hi: 32902473 ; 0x0e4: HOUR=0x9 DAYS=0xa MNTH=0x3 YEAR=0x7d8
kfdhdb.grpstmp.lo: 1396293632 ; 0x0e8: USEC=0x0 MSEC=0x270 SECS=0x33 MINS=0x14
kfdhdb.ub4spare[0]: 0 ; 0x0ec: 0x00000000
kfdhdb.ub4spare[1]: 0 ; 0x0f0: 0x00000000
kfdhdb.ub4spare[2]: 0 ; 0x0f4: 0x00000000
kfdhdb.ub4spare[3]: 0 ; 0x0f8: 0x00000000
kfdhdb.ub4spare[4]: 0 ; 0x0fc: 0x00000000
kfdhdb.ub4spare[5]: 0 ; 0x100: 0x00000000
kfdhdb.ub4spare[6]: 0 ; 0x104: 0x00000000
kfdhdb.ub4spare[7]: 0 ; 0x108: 0x00000000
kfdhdb.ub4spare[8]: 0 ; 0x10c: 0x00000000
kfdhdb.ub4spare[9]: 0 ; 0x110: 0x00000000
kfdhdb.ub4spare[10]: 0 ; 0x114: 0x00000000
kfdhdb.ub4spare[11]: 0 ; 0x118: 0x00000000
kfdhdb.ub4spare[12]: 0 ; 0x11c: 0x00000000
kfdhdb.ub4spare[13]: 0 ; 0x120: 0x00000000
kfdhdb.ub4spare[14]: 0 ; 0x124: 0x00000000
kfdhdb.ub4spare[15]: 0 ; 0x128: 0x00000000
kfdhdb.ub4spare[16]: 0 ; 0x12c: 0x00000000
kfdhdb.ub4spare[17]: 0 ; 0x130: 0x00000000
kfdhdb.ub4spare[18]: 0 ; 0x134: 0x00000000
kfdhdb.ub4spare[19]: 0 ; 0x138: 0x00000000
kfdhdb.ub4spare[20]: 0 ; 0x13c: 0x00000000
kfdhdb.ub4spare[21]: 0 ; 0x140: 0x00000000
kfdhdb.ub4spare[22]: 0 ; 0x144: 0x00000000
kfdhdb.ub4spare[23]: 0 ; 0x148: 0x00000000
kfdhdb.ub4spare[24]: 0 ; 0x14c: 0x00000000
kfdhdb.ub4spare[25]: 0 ; 0x150: 0x00000000
kfdhdb.ub4spare[26]: 0 ; 0x154: 0x00000000
kfdhdb.ub4spare[27]: 0 ; 0x158: 0x00000000
kfdhdb.ub4spare[28]: 0 ; 0x15c: 0x00000000
kfdhdb.ub4spare[29]: 0 ; 0x160: 0x00000000
kfdhdb.ub4spare[30]: 0 ; 0x164: 0x00000000
kfdhdb.ub4spare[31]: 0 ; 0x168: 0x00000000
kfdhdb.ub4spare[32]: 0 ; 0x16c: 0x00000000
kfdhdb.ub4spare[33]: 0 ; 0x170: 0x00000000
kfdhdb.ub4spare[34]: 0 ; 0x174: 0x00000000
kfdhdb.ub4spare[35]: 0 ; 0x178: 0x00000000
kfdhdb.ub4spare[36]: 0 ; 0x17c: 0x00000000
kfdhdb.ub4spare[37]: 0 ; 0x180: 0x00000000
kfdhdb.ub4spare[38]: 0 ; 0x184: 0x00000000
kfdhdb.ub4spare[39]: 0 ; 0x188: 0x00000000
kfdhdb.ub4spare[40]: 0 ; 0x18c: 0x00000000
kfdhdb.ub4spare[41]: 0 ; 0x190: 0x00000000
kfdhdb.ub4spare[42]: 0 ; 0x194: 0x00000000
kfdhdb.ub4spare[43]: 0 ; 0x198: 0x00000000
kfdhdb.ub4spare[44]: 0 ; 0x19c: 0x00000000
kfdhdb.ub4spare[45]: 0 ; 0x1a0: 0x00000000
kfdhdb.ub4spare[46]: 0 ; 0x1a4: 0x00000000
kfdhdb.ub4spare[47]: 0 ; 0x1a8: 0x00000000
kfdhdb.ub4spare[48]: 0 ; 0x1ac: 0x00000000
kfdhdb.ub4spare[49]: 0 ; 0x1b0: 0x00000000
kfdhdb.ub4spare[50]: 0 ; 0x1b4: 0x00000000
kfdhdb.ub4spare[51]: 0 ; 0x1b8: 0x00000000
kfdhdb.ub4spare[52]: 0 ; 0x1bc: 0x00000000
kfdhdb.ub4spare[53]: 0 ; 0x1c0: 0x00000000
kfdhdb.ub4spare[54]: 0 ; 0x1c4: 0x00000000
kfdhdb.ub4spare[55]: 0 ; 0x1c8: 0x00000000
kfdhdb.ub4spare[56]: 0 ; 0x1cc: 0x00000000
kfdhdb.ub4spare[57]: 0 ; 0x1d0: 0x00000000
kfdhdb.acdb.aba.seq: 0 ; 0x1d4: 0x00000000
kfdhdb.acdb.aba.blk: 0 ; 0x1d8: 0x00000000
kfdhdb.acdb.ents: 0 ; 0x1dc: 0x0000
kfdhdb.acdb.ub2spare: 0 ; 0x1de: 0x0000


Case 2:
Logical device id or LUN id is different across the nodes?:

Please refer to above session j for fix


Case 3:
Missing disk(can't mount diskgroup/drop diskgroup/delete disk)?:

Problem:
Why am I getting following errors, when I am doing "alter diskgroup ORADB_DATA_DG mount;" command?


ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "7" is missing


Consider all the above mentioned missing disk possibilities to fix the problem. If it's permanent disk failures, please refer below "Recovering the disks from permanent disk failures? session"


I
Recovering the disks from permanent disk failures?


10g:
Method 1.

Use DD command to clear disk header and delete the disk and create the disk, which will make the disks as candidates and add back to ASM diskgroup.

dd if=/dev/zero of=[raw device] bs=1024 count=4 -- for raw device
or
dd if=/dev/zero of=[emcpower device] bs=1024 count=4 -- for emcpower device

as root only!!!
/etc/init.d/oracleasm deletedisk [ASM disk name]
/etc/init.d/oracleasm createdisk [ASM disk name] [emcpower device]

/etc/init.d/oracleasm scandisks

Method 2.
create diskgroup EXTERNAL REDUNDANCY
disk '<1'st missing disk name>' force, '<2'nd missing disk name>' force;

Now the question is how do I drop the original diskgroup?
When you dd or force all the disks to use new diskgroup, old ASM diskgroup will not exist any longer. byeee, byee bye


11g:
drop diskgroup force including contents; -- Drop the diskgroup completely, to recreate the diskgroup.

alter diskgroup mount force; -- This is useful when you have missing disk or disks


Side Note: You can not use the FORCE flag when dropping a disk from an external redundancy diskgroup.