otak-otak | favorite evening food

February 14, 2015

bash profile for multiple oracle instances (standalone databases) on linux

Filed under: script — ora62 @ 7:27 am

Got an environment with 15 instances (15 – databases) on a host, ya this is an ideal environment for consolidations, but pre-12c which will introduce many ‘background processes’ and many overheads.

In term of adminitration and monitoring I created a bash function to simplify startup, shutdown, check status – colored whether db is up (green) or down (red), run common sql, check alertlog based on instance name supplied.

Beware the help is in Indonesian, please call google translate to get help :)

 

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=`hostname`; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/dbhome_1; export ORACLE_HOME
ORACLE_SID=db1; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=$PATH:$HOME/bin:/usr/sbin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

alias sq=’sqlplus / as sysdba’

function alert {
SID=$1
export ORACLE_SID=$SID
unset testvar
testvar=$(sqlplus -s / as sysdba <<!
set pagesize 0 feedback off verify off heading off echo off;
select value from v\$parameter where name=’background_dump_dest’;
exit;
!
echo $testvar
)
tail -200f ${testvar}/alert_${SID}.log
}

function dbstatus {
SID=$1
OPT=$2
export ORACLE_SID=$SID

unset testvar2
testvar2=$(sqlplus -s / as sysdba <<!
set pagesize 0 feedback off verify off heading off echo off;
select status from v\$instance;
exit;
!
)
echo -e “$SID \t=> ${testvar2}”
}

function dbup {
SID=$1
OPT=$2
export ORACLE_SID=$SID

unset testvar8
testvar8=$(sqlplus -s / as sysdba <<!
set pagesize 0 feedback off verify off heading off echo off;
startup $OPT;
exit;
!
)
dbstatus $SID
}

function dbdown {
SID=$1
export ORACLE_SID=$SID
unset testvar3
testvar3=$(sqlplus -s / as sysdba <<!
shutdown immediate;
exit;
!
)
CK1=$(ps -ef|grep ora_smon_${SID} | grep -v grep | wc -l)
if [ ${CK1} -eq 0 ]; then echo “$SID was down”; else echo “$SID still running”; fi
}

function dbora {
CMD=$1
SIDS=$2
OPTS=$3

declare -a DBS=(‘db1’ ‘db2’ ‘db3’ ‘db4’
‘db5’ ‘db6’ ‘db7’ ‘db8’
‘db9’ ‘db10’ ‘db11’ ‘db12’
‘db13’ ‘db14’ ‘db15’);

if [ ${CMD} == “start” ]; then

if [ ${SIDS} == “all” ]; then
for I in ${DBS[@]}
do
dbup $I $OPTS
done
else
dbup $SIDS $OPTS
fi
fi

if [ ${CMD} == “down” ]; then
if [ ${SIDS} == “all” ]; then
for I in ${DBS[@]}
do
dbdown $I
done
else
dbdown $SIDS
fi
fi

if [ ${CMD} == “list” ]; then
for I in ${DBS[@]}
do
CK2=$(ps -ef|grep ora_pmon_${I} | grep -v grep|wc -l)
if [ ${CK2} -ne 0 ]; then
echo -e “\e[38;5;82m${I} \t: Up”
else
echo -e “\e[38;5;196m${I} \t: Down”
fi
done
echo -e “\e[38;5;247m”

fi

if [ ${CMD} == “status” ]; then
for I in ${DBS[@]}
do
CK2=$(ps -ef|grep ora_pmon_${I} | grep -v grep|wc -l)
if [ ${CK2} -ne 0 ]; then
dbstatus $I
else
echo -e “\e[38;5;196m${I} \t: Down”
fi
echo -n -e “\e[38;5;247m”
done

fi

if [ ${CMD} == “alert” ] && [ -n “${SIDS}” ]; then
CK8=$(ps -ef|grep ora_pmon_${SIDS} | grep -v grep|wc -l)
if [ ${CK8} -ne 0 ]; then
alert ${SIDS}
else
echo -e “\e[38;5;196m${SIDS} \t: Down”
echo -e “\e[38;5;247m”
fi
fi

if [ ${CMD} == “sql” ] && [ -n “${SIDS}” ]; then
CK9=$(ps -ef|grep ora_pmon_${SIDS} | grep -v grep|wc -l)
if [ ${CK9} -ne 0 ]; then
export ORACLE_SID=${SIDS}
if [ -n “${OPTS}” ]; then
sqlplus / as sysdba @${OPTS}
else
sqlplus / as sysdba
fi
else
echo -e “\e[38;5;196m${SIDS} \t: Down”
echo -e “\e[38;5;247m”
fi
fi

if [ ${CMD} == “help” ]; then
echo “cara pemakaian:”
echo “dbora list ==> list + up/down database”
echo “dbora status ==> list + status database”
echo “dbora start all [OPTION] ==> startup semua database berdasarkan OPTION [mount/open/restrict/nomount]”
echo “dbora start [SID] [OPTION] ==> startup satu database berdasarkan nama instance OPTION [mount/open/restrict/nomount]”
echo “dbora down all ==> shutdown semua database”
echo “dbora down [SID] ==> shutdown satu database berdasarkan nama instancenya”
echo “dbora alert [SID] ==> melihat alertlog file berdasarkan nama instancenya”
echo “dbora sql [SID] ==> sqlplus / as sysdba berdasarkan nama instancenya”
echo “dbora sql [SID] [file.sql] ==> sqlplus / as sysdba berdasarkan nama instancenya dan eksekusi file.sql”
fi

}

Advertisements

February 4, 2012

Script to fix streams error at apply side

Filed under: streams — ora62 @ 8:24 am

The script is one of my streams implementation and can save my time to not always keep catching on the dba_apply_error.

CREATE OR REPLACE PROCEDURE fix_err_dtl(in_any IN SYS.ANYDATA, arg_object_owner varchar2, arg_object_name varchar2) IS

lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
object_owner VARCHAR2(30);
object_name VARCHAR2(40);
dmlcommand VARCHAR2(10);
i NUMBER;
l_dmlcommand varchar2(20);
l_dummy number;
lcrrow sys.lcr$_row_list;
pkdata sys.anydata;
pknumber number;
pkdate date;
pkchar10 char(10);
pkchar4 char(4);
pkchar1 char(1);
pkvarchar2 VARCHAR2(32000);

-- field1 through field3 is unique/pk index for table test_control
l_field1 number;
l_field2 number;
l_field3 varchar2(100);

BEGIN
rc := in_any.GETOBJECT(lcr);
object_owner := lcr.GET_OBJECT_OWNER();
object_name := lcr.GET_OBJECT_NAME();
dmlcommand := lcr.GET_COMMAND_TYPE();
IF arg_object_owner='TEST' and arg_object_name='TEST_CONTROL' then

IF dmlcommand in ('INSERT','UPDATE','DELETE') THEN
if dmlcommand in ('UPDATE','DELETE') THEN
lcrrow := lcr.GET_VALUES('OLD');
elsif dmlcommand = 'INSERT' THEN
lcrrow := lcr.GET_VALUES('NEW');
end if;

for i in 1..lcrrow.count loop
if lcrrow(i).column_name = 'FIELD1' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getnumber(pknumber);
l_field1 := pknumber;
elsif lcrrow(i).column_name = 'FIELD2' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getnumber(pknumber);
l_field2 := pknumber;
elsif lcrrow(i).column_name = 'FIELD3' THEN
pkdata := lcrrow(i).data;
rc := pkdata.getvarchar2(pkvarchar2);
l_field3 := pkvarchar2;
end if;
end loop;

begin
select count(*) into l_dummy
from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;

if l_dummy >= 1 then
if dmlcommand = 'INSERT' then
delete from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;
elsif dmlcommand in ('UPDATE','DELETE') and l_dummy >= 1 then
delete from TEST.TEST_CONTROL
where field1=l_field1
and field2=l_field2
and field3=l_field3;

insert into TEST.TEST_CONTROL(field1,field2,field3)

values(l_field1,l_field2,l_field3);

end if;
elsif l_dummy = 0 then
if dmlcommand in ('UPDATE','DELETE') then
insert into TEST.TEST_CONTROL(field1,field2,field3)

values(l_field1,l_field2,l_field3);
end if;
end if;
end;
END IF;
elsIF arg_object_owner='TEST' and arg_object_name='TEST2' then
...........
...........
...........
...........
end if; -- if last
END;
/

the above procedure will be called by this procedure:

CREATE or replace PROCEDURE fix_err(arg_apply_name varchar2) IS

cursor c1 is select LOCAL_TRANSACTION_ID, MESSAGE_NUMBER, MESSAGE_COUNT
FROM DBA_APPLY_ERROR where apply_name=arg_apply_name order by SOURCE_COMMIT_SCN;

i NUMBER;
lcr ANYDATA;
lcr2 sys.lcr$_row_record;
lcrrow sys.lcr$_row_list;
rc2 pls_integer;
l_objname varchar2(50);
l_object_owner varchar2(50);
l_MESSAGE_NUMBER number;
l_LOCAL_TRANSACTION_ID varchar2(50);
l_msg varchar2(100);
BEGIN
for h in c1 loop
for j in 1..h.MESSAGE_COUNT loop
begin
select MESSAGE_NUMBER,LOCAL_TRANSACTION_ID into

l_MESSAGE_NUMBER, l_LOCAL_TRANSACTION_ID
from dba_apply_error
where LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID;

if l_MESSAGE_NUMBER is null or l_MESSAGE_NUMBER=0 then
begin
dbms_apply_adm.execute_error(h.LOCAL_TRANSACTION_ID);
exception
when others then
null;
end;
elsif j = l_MESSAGE_NUMBER then
lcr := NULL;
lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(j,

h.LOCAL_TRANSACTION_ID);
rc2 := lcr.getobject(lcr2);
l_objname := lcr2.GET_OBJECT_NAME;
l_object_owner := lcr2.GET_OBJECT_OWNER();

fix_err_dtl(lcr, l_object_owner, l_objname);

begin
i:=h.MESSAGE_COUNT;
dbms_apply_adm.execute_error(h.LOCAL_TRANSACTION_ID);
exception
when others then
select MESSAGE_NUMBER into i
from dba_apply_error
where LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID;

if l_LOCAL_TRANSACTION_ID=h.LOCAL_TRANSACTION_ID

and i<>l_MESSAGE_NUMBER then
null;
else
l_msg := ' id: '||h.LOCAL_TRANSACTION_ID||' num: '||to_char(i);
raise_application_error(-20001,'err: '||SQLCODE||' -ERROR- '||SQLERRM||l_msg);
end if;
end;
end if;
exception
when no_data_found then
null;
end;
end loop;
commit;
end loop;
commit;
END;
/

and finally create scheduler by interval 10 secs for this fix error apply:

conn strmadmin/passwd

create procedure fix_strm_err is
l_status varchar2(10);
l_cnt number;
l_name varchar2(50) := 'BPK_APPLY';
begin
select count(1) into l_cnt from dba_apply_error where apply_name=l_name;

if l_cnt >= 1 then
fix_err(l_name);
end if;

select status into l_status
from dba_apply where apply_name=l_name;

if l_status<>'ENABLED' then
dbms_apply_adm.start_apply(l_name);
end if;

end;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'PRG_FIX_STRM'
,program_type => 'PLSQL_BLOCK'
,program_action => 'begin fix_strm_err; end;'
,number_of_arguments => 0
,enabled => FALSE
,comments => NULL
);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'PRG_FIX_STRM');
END;
/
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'JOB_FIX_STRM'
,start_date => TO_TIMESTAMP_TZ('2012/02/01 16:59:59.467189 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=SECONDLY;INTERVAL=10'
,end_date => NULL
,program_name => 'PRG_FIX_STRM'
-- ,job_class => 'BATCH_JOB_CLASS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'RESTARTABLE'
,value => TRUE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
--SYS.DBMS_SCHEDULER.LOGGING_FULL
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'MAX_RUNS');
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
EXCEPTION
-- could fail if program is of type EXECUTABLE...
WHEN OTHERS THEN
NULL;
END;
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'JOB_PRIORITY'
,value => 1);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'JOB_FIX_STRM'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'JOB_FIX_STRM'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'JOB_FIX_STRM');
END;
/

happy streaming….:)

December 30, 2011

ORA-600 17182 Block corruption

Filed under: troubleshoot — ora62 @ 2:12 pm

Suddenly one of my customer make a call at night about ora-600 of their core system…

logging into machine and grep the alert log, found the ora-600; database can be opened for a while (about 2 minutes) and then terminate.

Doing block recovery for file 7 block 15312
Block recovery from logseq 147867, block 3707 to scn 3789052035
Recovery of Online Redo Log: Thread 1 Group 6 Seq 147867 Reading mem 0
Mem# 0: /redolog1/dbname/redo06.log
Block recovery stopped at EOT rba 147867.3776.16
Block recovery completed at rba 147867.3776.16, scn 0.3789052035
Thu Dec 29 21:35:26 2011
Completed: alter database open
Thu Dec 29 21:35:29 2011
Starting background process CJQ0
Thu Dec 29 21:35:29 2011
CJQ0 started with pid=45, OS id=192
Thu Dec 29 21:35:29 2011
Sweep Incident[95434]: completed
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc:
SVR4 Error: 2: No such file or directory
Additional information: 3
ORACLE Instance dbname (pid = 13) – Error 600 encountered while recovering transaction (203, 27) on object 79208.
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc:
ORA-00600: internal error code, arguments: [17182], [0xFFFFFFFF7B81D750], [], [], [], [], [], []
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc (incident=95435):
ORA-00600: internal error code, arguments: [KSMFPG2], [0xFFFFFFFF7B81D000], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17182], [0xFFFFFFFF7B81D750], [], [], [], [], [], []
Incident details in: /oracle/diag/diag/rdbms/dbnameg/dbname/incident/incdir_95435/dbname_smon_29913_i95435.trc
Thu Dec 29 21:35:32 2011
Trace dumping is performing id=[cdmp_20111229213532]
Thu Dec 29 21:35:33 2011
Fatal internal error happened while SMON was doing active transaction recovery.
Errors in file /oracle/diag/diag/rdbms/dbnameg/dbname/trace/dbname_smon_29913.trc:
ORA-00600: internal error code, arguments: [KSMFPG2], [0xFFFFFFFF7B81D000], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [17182], [0xFFFFFFFF7B81D750], [], [], [], [], [], []
SMON (ospid: 29913): terminating the instance due to error 474
Instance terminated by SMON, pid = 29913

from the alert log I found there are corruption at block level, but oracle actualy can recovered it automatically, but it seems when there is undo application for a transaction got stuck due to block corrupt at the other block.

find the object name as informed by alertlog:

select owner,object_name from dba_objects where object_id=79208;

now time to scan all the logical corruption by rman validate command:

run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

confirmed there are 2 block corruption on the table:

Error backing up file 7, block 15312: logical corruption
Error backing up file 7, block 18714: logical corruption

another check by dbms_repair to investigate the real corruption, and marked it as corrupt:
declare
begin
dbms_repair.admin_tables(
table_name => ‘REPAIR_TABLE_99’,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’);
end;
/
select owner,object_name,object_type
from dba_objects
where object_name like ‘%REPAIR_TABLE%’;

declare
begin
dbms_repair.admin_tables(
table_name => ‘ORPHAN_REP_TBL_99’,
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’);
end;
/

select owner,object_name,object_type
from dba_objects
where object_name like ‘%ORPHAN%’;

set serveroutput on

declare
rpr_count int;
begin
rpr_count :=0;

dbms_repair.check_object(
schema_name => ‘TEST’,
object_name => ‘TBL1’,
repair_table_name => ‘REPAIR_TABLE_99’,
corrupt_count => rpr_count);

dbms_output.put_line(‘rep cnt : ‘||to_char(rpr_count));
end;
/

select object_name,block_id,corrupt_type,marked_corrupt,corrupt_description,repair_description from repair_table_99;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
—————————— ———- ———— ———-
CORRUPT_DESCRIPTION
——————————————————————————–
REPAIR_DESCRIPTION
——————————————————————————–
TBL1 15312 6130 FALSE
Block Checking: DBA = 29375440, Block Type = KTB-managed data block
data header at 0x3d85b007c
kdbchk: fseo(223) < fsbo(282)
mark block software corrupt
TBL1 18714 6110 FALSE
Block Checking: DBA = 29378842, Block Type = KTB-managed data block
data header at 0x3d756e0ac
kdbchk: the amount of space used is not equal to block size
used=7403 fsc=0 avsp=769 dtl=8016
mark block software corrupt

mark those block as corrupted:

declare
l_fix_count int;
begin
l_fix_count:=0;

dbms_repair.fix_corrupt_blocks(
schema_name => ‘TEST’,
object_name => ‘TBL1’,
object_type => dbms_repair.table_object,
repair_table_name => ‘REPAIR_TABLE_99’,
fix_count => l_fix_count);

dbms_output.put_line(‘fix cnt : ‘||to_char(l_fix_count));
end;
/

select object_name,block_id,corrupt_type,marked_corrupt,corrupt_description,repair_description from repair_table_99;

OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR
—————————— ———- ———— ———-
CORRUPT_DESCRIPTION
——————————————————————————–
REPAIR_DESCRIPTION
——————————————————————————–
TBL1 15312 6130 TRUE
Block Checking: DBA = 29375440, Block Type = KTB-managed data block
data header at 0x3d85b007c
kdbchk: fseo(223) < fsbo(282)
mark block software corrupt

TBL1 18714 6110 TRUE
Block Checking: DBA = 29378842, Block Type = KTB-managed data block
data header at 0x3d756e0ac
kdbchk: the amount of space used is not equal to block size
used=7403 fsc=0 avsp=769 dtl=8016
mark block software corrupt

now time to recover the table
skip corrupted block from reading, and get the good data:

alter session set event=’10231 trace name context forever, level 10’;
create table test.tmp as select * from test.tbl1;

ok done, now the application guy who have to look into the data and fix them
have a nice sleep then :)

December 1, 2011

perl script to automate restore, recover rman backup

Filed under: barman — ora62 @ 11:26 am

I created ‘simple-silly’ perl script to automate restore and recover of rman backup. this script will only need supply rman backup log.

how to execute:
1. to restore & recover until all archivelog in the backup.
./restore_arc.sh <full_path_rman_backup_log>

2. to restore & recover until all archivelog (until cancel).
./restore_arc_last.sh <full_path_rman_backup_log>

has been tested on linux env, you may use this script for your needs and please take your own risk :)

cat restore_arc.sh

#!/bin/bash
. /home/ujang/.bashrc

SCRPATH=/home/ujang/docs/orascript/backup-recovery/rman
LOGPATH=/home/ujang/docs/orascript/backup-recovery/rman
TAG=`date +%Y%m%d`
CMDFILE=${SCRPATH}/restore.txt
LOGFILE=${LOGPATH}/restore_${TAG}.log
RMANLOG=$1
cd $SCRPATH
export ORACLE_HOME=/apps/oracle/product/9.2
export ORACLE_SID=PROD
./restore.pl $RMANLOG spfile=NO lastarc=NO
##$ORACLE_HOME/bin/rman target / nocatalog cmdfile=$CMDFILE msglog $LOGFILE

cat restore_arc_last.sh

#!/bin/bash
. /home/ujang/.bashrc

SCRPATH=/home/ujang/docs/orascript/backup-recovery/rman
LOGPATH=/home/ujang/docs/orascript/backup-recovery/rman
TAG=`date +%Y%m%d`
CMDFILE=${SCRPATH}/restore.txt
LOGFILE=${LOGPATH}/restore_${TAG}.log
RMANLOG=$1
cd $SCRPATH
export ORACLE_HOME=/apps/oracle/product/9.2
export ORACLE_SID=PROD
./restore.pl $RMANLOG spfile=NO lastarc=YES
##$ORACLE_HOME/bin/rman target / nocatalog cmdfile=$CMDFILE msglog $LOGFILE

cat restore.pl

#!/usr/bin/perl
my @array = qq[input archive log thread];
my @match = (“sequence=”,”thread=”,”DBID=”,”piece handle=”,”CONTROL”,”SPFILE”);
my @seqid;
my $thread=0;
my $dbid=0;
my $ctlfile=”XXX”;
my $initfile=”XXX”;
my $orahome=”XXX”;
my $orasid=”X”;
foreach $key (keys(%ENV)) {
if ($orahome eq “XXX”) {
if ($key eq “ORACLE_HOME”) {
$orahome=$ENV{$key};
}
}
if ($orasid eq “X”) {
if ($key eq “ORACLE_SID”) {
$orasid=$ENV{$key};
}
}
}

if ($orahome eq “XXX” || $orasid eq “X”)
{
print “environment variable ORACLE_HOME dan ORACLE_SID harus diset!!\n”;
exit 0;
}

if ($ARGV[0] eq “help=yes” || $ARGV[0] eq “help=y” || $ARGV[0] eq “-h” || $ARGV[0] eq “–h”)
{
print “Cara penggunaan :\n”;
print “1. anda harus berada pada directory dimana file ini dijalankan, misalnya cd /oradata/rman/scr”;
print “2. ketik \n”;
print ” recover spfile=YES|NO\n”;
print ” misal: \n”;
print ” recover /oradata/rman/log/rman_bkp_20110504.log spfile=NO\n”;
exit 0;
}

my $rmanlog=$ARGV[0];
my $spfileloc=$ARGV[1];
my $lastarc=$ARGV[2];
my $spfileloc=substr($spfileloc,index($spfileloc,”=”,0)+1,3);
my $lastarc=substr($lastarc,index($lastarc,”=”,0)+1,3);

if (length($rmanlog)<=0)
{
print “anda harus passing parameter, gunakan restore.sh -h untuk bantuan\n”;
exit 0;
}

open(OLD, “<“, $rmanlog) or die “can’t open $rmanlog”;
while ()
{
my $line = $_;
if ($dbid == 0) {
my $seq = (split /$match[2]/, $line)[1];
$dbid=substr($seq,0,index($seq,”)”,0));
}
if ($ctlfile eq “XXX”) {
my $seq = (split /^$match[3]/, $line)[1];
if($seq =~ m/$match[4]/i) {
$ctlfile=substr($seq,0,index($seq,chr(32),0));
}
}

if ($initfile eq “XXX”) {
my $seq = (split /$match[3]/, $line)[1];
if($seq =~ m/$match[5]/i) {
$initfile=substr($seq,0,index($seq,chr(32),0));
}
}
if ($line =~ /^@array/) {
my $seq = (split /$match[0]/, $line)[1];
push @seqid, substr($seq,0,index($seq,chr(32),0));
if ($thread == 0) {
my $seq = (split /$match[1]/, $line)[1];
$thread=substr($seq,0,index($seq,chr(32),0));
}
}
}
@seqid=sort(@seqid);
my $seqmax = @seqid;
close(OLD);

system(“rm -f restore.txt”);

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year=$year+1900;
$new=”restore.txt”;
open(NEW, “>”, $new) or die “can’t open $new: $!”;
if ($spfileloc eq “YES” or $spfileloc eq “yes”) {
print NEW “host ‘mv $orahome/dbs/spfile$orasid\.ora $orahome/dbs/spfile$orasid\_$year$mon$mday$hour$min\.ora’;\n” or die “can’t write $new: $!”;
}
print NEW “set dbid=$dbid;\n” or die “can’t write $new: $!”;
print NEW “startup force nomount;\n” or die “can’t write $new: $!”;
if ($spfileloc eq “YES” or $spfileloc eq “yes”) {
print NEW “restore spfile to ‘$orahome/dbs/spfile$orasid\.ora’ from ‘$initfile’;\n” or die “can’t write $new: $!”;
}
print NEW “shutdown abort;\n” or die “can’t write $new: $!”;
print NEW “set dbid=$dbid;\n” or die “can’t write $new: $!”;
print NEW “startup nomount;\n” or die “can’t write $new: $!”;
print NEW “restore controlfile from ‘$ctlfile’;\n” or die “can’t write $new: $!”;
print NEW “alter database mount;\n” or die “can’t write $new: $!”;
print NEW “restore database;\n” or die “can’t write $new: $!”;
print NEW “restore archivelog from sequence $seqid[0] until sequence $seqid[$seqmax-1];\n” or die “can’t write $new: $!”;

if ($lastarc eq “YES” or $lastarc eq “yes”) {
print NEW “sql ‘RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;’;\n” or die “can’t write $new: $!”;
} else {
print NEW “recover database until logseq $seqid[$seqmax-1] thread $thread;\n” or die “can’t write $new: $!”;
}
print NEW “sql ‘alter database open resetlogs’;\n” or die “can’t write $new: $!”;
close(NEW) or die “can’t close $new: $!”;

November 10, 2011

HW – Contention evil

Filed under: Troubleshoot Tuning — ora62 @ 1:26 pm

Suddenly my old friend call me on 10PM, he asked about HW contention and row cache object + free global transaction table entry which makes their enterprise system “stuck”.

From AWR, the insert statement on a BLOB column can reach 101 seconds, hence makes database call from application server to database slower. Log in to the system and looking for v$session_wait, found a session who execute ‘rollback force’ and hung, I decided to kill this problematic session.

By querying v$session_wait sampled every 3 seconds, found HW – contention from the P3, decode to the file number and block number:

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(771921779) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(771921779) BLOCK#
from dual;

What is the segments which contending on HW:

select owner, segment_type, segment_name
from dba_extents
where file_id = 184
and 169843 between block_id and block_id + blocks - 1; 

Internal application server engine saves state of every transaction to BLOB, and this above query result point to that BLOB. Insertion to this column will wait and finish after 101 seconds. 128 Hash partition on this segments can help at least for a while, and luckily there is no such documentation on the NET about how the best way to partition this table, this table has a column using unix date.

May 9, 2011

resource manager 80% cpu only

Filed under: troubleshoot — ora62 @ 7:53 am

here the steps of configuring resource manager to only gives oracle 80% cpu only for all the user sessions belongs to GROUP_OLTP.

set serverout on size 5555
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN('PLAN_OLTP');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP('GROUP_OLTP');
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group=>'GROUP_OLTP',comment=>'oltp rsrc');
dbms_resource_manager.create_plan(plan=>'PLAN_OLTP',comment=>'batch plan');
dbms_resource_manager.create_plan_directive(plan=>'PLAN_OLTP',group_or_subplan=>'GROUP_OLTP',comment=>'plan dir night',cpu_p1=>80,parallel_degree_limit_p1=>16);
dbms_resource_manager.create_plan_directive(plan=>'PLAN_OLTP',group_or_subplan=>'OTHER_GROUPS',comment=>'plan dir night',cpu_p3=>80,parallel_degree_limit_p1=>8);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
/
commit
/
begin
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SCOTT',consumer_group=>'GROUP_OLTP',grant_option=>false);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SH',consumer_group=>'GROUP_OLTP',grant_option=>false);
dbms_resource_manager.set_initial_consumer_group(user=>'SCOTT',consumer_group=>'GROUP_OLTP');
dbms_resource_manager.set_initial_consumer_group(user=>'SH',consumer_group=>'GROUP_OLTP');
end;
/
alter system set resource_manager_plan=PLAN_OLTP scope=both
/
alter session set resource_manager_plan=PLAN_OLTP scope=both
/

set lines 200
COL consumer_group      FORMAT A24  HEADING 'Consumer Group'
COL cpu_method          FORMAT A18  HEADING 'CPU Method'
COL status              FORMAT A10  HEADING 'Status'
COL mandatory           FORMAT A06  HEADING 'Manda-|tory?'
COL comments            FORMAT A32  HEADING 'Comments'
SELECT
     consumer_group
    ,cpu_method
    ,status
    ,mandatory
    ,comments
  FROM dba_rsrc_consumer_groups;
 
COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL num_plan_directives       FORMAT 9999 HEADING '# of|Plan|Dirs'
COL cpu_method                FORMAT A18  HEADING 'CPU Method'
COL active_sess_pool_mth      FORMAT A32  HEADING 'Active|Session|Pool|Method'
COL parallel_degree_limit_mth FORMAT A32  HEADING 'Parallel|Limit|Method'
COL queueing_mth              FORMAT A18  HEADING 'Queueing|Method'
COL status                    FORMAT A10  HEADING 'Status'
COL mandatory                 FORMAT A06  HEADING 'Manda-|tory?'
COL comments                  FORMAT A32  HEADING 'Comments'
SELECT
     plan
    ,num_plan_directives
    ,cpu_method
    ,active_sess_pool_mth
    ,parallel_degree_limit_mth
    ,queueing_mth
    ,status
    ,mandatory
  FROM dba_rsrc_plans
;
 
COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL group_or_subplan          FORMAT A12  HEADING 'Group or|SubPlan'
COL type                      FORMAT A15  HEADING 'Type'
COL cpu_p1                    FORMAT 999  HEADING 'CPU|1 %'
COL cpu_p2                    FORMAT 999  HEADING 'CPU|2 %'
COL cpu_p3                    FORMAT 999  HEADING 'CPU|3 %'
COL status                    FORMAT A08  HEADING 'Status'
COL mandatory                 FORMAT A06  HEADING 'Manda-|tory?'
COL comments                  FORMAT A24  HEADING 'Comments'

SELECT
     plan
    ,group_or_subplan
    ,type
    ,cpu_p1
    ,cpu_p2
    ,cpu_p3
    ,status
  FROM dba_rsrc_plan_directives
 WHERE plan = 'PLAN_OLTP'
;

COL initial_rsrc_consumer_group  FORMAT A24  HEADING 'Resource|Consumer|Group'
COL username                     FORMAT A12  HEADING 'User Name'
SELECT
	 initial_rsrc_consumer_group,
    username
  FROM dba_users
 ORDER BY 1,2;

SELECT se.sid sess_id, co.name consumer_group,
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

streams tuning on high batch jobs

Filed under: streams — ora62 @ 7:19 am
this apply parameter in streams environment is intended for the machine that has many cpus. 
the parallelis and commit_serialization will help when there are many insert, 
the streams will extremely fast enough to handle huge transaction.
but if your transaction is mandatory by update or delete, commit_serialization will depends on the scn order. 
so there are no impact to your business (but it should need careful test)

BEGIN
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => 'TXN_LCR_SPILL_THRESHOLD',
   value => '15000000');

DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_TXN_BUFFER_SIZE',
   value => '64');
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_hash_table_size',
   value => '10000000');
dbms_apply_adm.set_parameter('A_NONAME','_SGA_SIZE','8192');
dbms_apply_adm.set_parameter('A_NONAME', '_RESTRICT_ALL_REF_CONS','N');
dbms_apply_adm.set_parameter('A_NONAME', 'COMMIT_SERIALIZATION','NONE');
dbms_apply_adm.set_parameter('A_NONAME', 'PARALLELISM','64');

END;
/

exec dbms_apply_adm.stop_apply('A_NONAME');

exec dbms_apply_adm.start_apply('A_NONAME');

April 21, 2011

BITMAP CONVERSION TO ROWIDS

Filed under: sql tuning — ora62 @ 8:54 am

on 10.2.0.5 AIX

The query is really simple just querying a table filtered by combination of columns, the table has 2 indexes (each 1 column) which is as part of where clause. from the plan, oracle seems to deal with just only cost, ya it is true cost is cheaper than by scanning only 1 index, but IO cost would likely double (scanning 2 indexes, bitmap and operation and than conver bitmap to rowid).

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |     1 |   112 |    66   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | T_4             |     1 |   112 |    66   (2)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                 |       |       |            |          |
|   3 |    BITMAP AND                    |                 |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IDX_1           | 25237 |       |     7   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IDX_0           | 25237 |       |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

here by force to use index 1,

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   112 |   501   (1)| 00:00:07 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_4             |     1 |   112 |   501   (1)| 00:00:07 |
|*  2 |   INDEX RANGE SCAN          | IDX_1           |  3801 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

it seems the cost is higher than bitmap conversion, but from the real execution time the second one is really faster. it seems cost calculation on the first plan is misleading.

April 19, 2011

Skip streams capture SCN

Filed under: streams — ora62 @ 8:16 pm

I have a nice experience on streams capture 10.2.0.1
when captures is set to grab all dml at the schema levels,
and suddenly user performed CTAS, and magically oracle
ora lucky number ORA-600 coming.

got error that capture builder has error, OK well done.
I started manually the capture process and after 10 minutes
PMON always kill capture builder process.

PMON has hidden parameter by default 10 minutes
to scan dead process.

no chance to do workaround as of metalink,
the note will state that database should be upgraded
to atleast 10.2.0.3…phiew

dong sync manualy from the latest applied SCN
and then skip capture SCN to start from
the last sync SCN :(

exec dbms_capture_adm.stop_capture(capture_name => 'AQ_CAPTURE');
delete from system.logmnr_restart_ckpt$ where spare1 < nnnn ;
exec dbms_capture_adm.alter_capture(capture_name => 'AQ_CAPTURE', first_scn => nnnn, start_scn => nnnn);
exec dbms_capture_adm.start_capture(capture_name => 'AQ_CAPTURE');

by looking at the alert log, luckily only objects belongs to sysman schema
got warning…which those are not captured.

January 24, 2009

Insert performance oracle 10.2

Filed under: benchmark — ora62 @ 4:23 pm

Bulk load insert, also application with high insert into a transaction table could lead to some bottleneck issue:
– log file sync issue, this kind of wait event due to frequent commit

– log buffer space issue, due to lack of log_buffer memory setting – free buffer waits, due to lack of db_cache_size memory setting

– buffer busy waits, this issue related to concurrency and transactions at the same table which being inserted, also sometimes too many indexes or indexes which has high clustering factors will lead to this wait event issue.

in fact if we doing direct path inserts, some issues can be avoided, such as free buffer waits, but wait event that related to redo is intact. in 10.2 there is a feature called asynchronous commit, which can alleviate redo wait event issue, but index issue still carryon even we using direct path.

there is trick approach which depends on your application behavior to boost insert performance.

– temporary table

– asynchronous commit (but be aware of recovery and data consistency)

– direct path

Older Posts »

Blog at WordPress.com.