Grep lines before or after without grep
Here a way I used in AIX, where there is a grep without the -A and -B options:
nawk 'c-->0;$0~s{if(b)for(c=b+1;c>1;c--)
print r[(NR-c+1)%b];print;c=a}b{r[NR%b]=$0}'
b=2 a=0 s="ORA-" alert_us.log
Oracle
GoldenGate replicat performance
Today at a client I had a old abended replicat process that I decided to give life to. So I solved the problem and start it.
The environment is using GoldenGate 11.1.1.1.2 against Oracle 10.2.0.4 database with ASM, on AIX. The replicat process replicates all operations of a 700+ tables schema. There were other replicats running at the same time writing to other schemas.
Then I decided to see the performance of GoldenGate. With the query:
GGSCI> stats r_xxxx TOTALSONLY * REPORTRATE sec
You get the detailed statistics of operations per second since the start of the process, since the beginning of the day and last hour for all tables replicated by that process. For instance:
*** Hourly statistics since 2012-04-16 13:00:00 ***
Total inserts/second: 1397.76
Total updates/second: 1307.46
Total deletes/second: 991.50
Total discards/second: 0.00
Total operations/second: 3696.71
So here we can see it is doing a bit more than 3500 operations per second, divided quite evenly between inserts, updates and deletes.
As usually the GoldenGate is used for realtime replication and there are no big operations, the client does not use performance related parameters. But this time I decided to play with them.
After adding both: BATCHSQL and INSERTAPPEND to the parameter file of the replicat process, the results were the following (after more than 10 minutes running) and performance is still increasing:
*** Hourly statistics since 2012-04-16 13:43:17 ***
Total inserts/second: 2174.92
Total updates/second: 2540.20
Total deletes/second: 1636.16
Total discards/second: 0.00
Total operations/second: 6351.28
We see the performance increased by 90% !
I got interested to see if the BATCHSQL parameter only by itself could make the difference. So I removed the INSERTAPPEND parameter (which only influences the inserts anyway). Here are the results after more than 10 minutes.
*** Hourly statistics since 2012-04-16 14:00:00 ***
Total inserts/second: 2402.21
Total updates/second: 2185.24
Total deletes/second: 1742.43
Total discards/second: 0.00
Total operations/second: 6329.88
Yep, seems the system of my client in certain situations benefits mostly of the BATCHSQL parameter.
For those who don’t know, “in BATCHSQL mode, Replicat organizes similar SQL statements into batches within a memory queue, and then it applies each batch in one database operation. A batch contains SQL statements that affect the same table, operation type (insert, update, or delete), and column list.” (in GoldenGate Reference Guide).
Oracle to MySQL migration – what you should know
A very nice presentation from Marco Tusa (Pythian) about the risk assessment of a Oracle to MySQL migration. Conclusion: think well and assess before moving an application to use MySQL instead of Oracle databases.
crs_stat and Oracle cluster resources status
One script output that Oracle to my knowledge did not improve yet was the “crs_stat -t”. On 11.2 there is the “./crsctl status resource -t“, but the output is quite long.
The usual output is like this:
hqbuun415:/oracle/app/product/11.1/crs/bin:crs:$crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....ISP1.cs application ONLINE ONLINE hqbuun415 ora....ss2.srv application ONLINE ONLINE hqbuun415 ora....ISP2.cs application ONLINE ONLINE hqbuun414 ora....ss1.srv application ONLINE ONLINE hqbuun414 ora....OAFO.cs application ONLINE ONLINE hqbuun414 ora....ss1.srv application ONLINE ONLINE hqbuun414 ora....M3PL.cs application ONLINE ONLINE hqbuun415 ora....ss2.srv application ONLINE ONLINE hqbuun415 ora....MALF.cs application ONLINE ONLINE hqbuun414 ora....ss1.srv application ONLINE ONLINE hqbuun414 ora....MAVE.cs application ONLINE ONLINE hqbuun415 ora....ss2.srv application ONLINE ONLINE hqbuun415 ora....WMEA.cs application ONLINE ONLINE hqbuun414 ora....ss1.srv application ONLINE ONLINE hqbuun414 ora....WMSS.db application ONLINE ONLINE hqbuun414 ora....s1.inst application ONLINE ONLINE hqbuun414 ora....s2.inst application ONLINE ONLINE hqbuun415 ora....SM1.asm application ONLINE ONLINE hqbuun414 ora....14.lsnr application ONLINE ONLINE hqbuun414 ora....414.gsd application ONLINE ONLINE hqbuun414 ora....414.ons application ONLINE ONLINE hqbuun414 ora....414.vip application ONLINE ONLINE hqbuun414 ora....SM2.asm application ONLINE ONLINE hqbuun415 ora....15.lsnr application ONLINE ONLINE hqbuun415 ora....415.gsd application ONLINE ONLINE hqbuun415 ora....415.ons application ONLINE ONLINE hqbuun415 ora....415.vip application ONLINE ONLINE hqbuun415
But on Metalink (My Oracle Support) note 259301.1 we can find the magic script (which works still with Oracle 11.2):
--------------------------- Begin Shell Script -------------------------------
#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment
RSC_KEY=$1
QSTAT=-u
AWK=/usr/xpg4/bin/awk # if not available use /usr/bin/awk
# Table header:echo ""
$AWK
'BEGIN {printf "%-45s %-10s %-18sn", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18sn", "-----------", "------", "-----";}'
# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18sn", appname, apptarget, appstate; state=0;}'
--------------------------- End Shell Script -------------------------------
This script makes a clear output as:
hqbuun414:/oracle/app/product/11.1/crs/bin:crs:$./crsstat HA Resource Target State ----------- ------ ----- ora.WMS01_WMSS.EISP1.cs ONLINE ONLINE on hqbuun415 ora.WMS01_WMSS.EISP1.wmss2.srv ONLINE ONLINE on hqbuun415 ora.WMS01_WMSS.EISP2.cs ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.EISP2.wmss1.srv ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.NESOAFO.cs ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.NESOAFO.wmss1.srv ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.WM3PL.cs ONLINE ONLINE on hqbuun415 ora.WMS01_WMSS.WM3PL.wmss2.srv ONLINE ONLINE on hqbuun415 ora.WMS01_WMSS.WMALF.cs ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.WMALF.wmss1.srv ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.WMAVE.cs ONLINE ONLINE on hqbuun415 ora.WMS01_WMSS.WMAVE.wmss2.srv ONLINE ONLINE on hqbuun415 ora.WMS01_WMSS.WMEA.cs ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.WMEA.wmss1.srv ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.db ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.wmss1.inst ONLINE ONLINE on hqbuun414 ora.WMS01_WMSS.wmss2.inst ONLINE ONLINE on hqbuun415 ora.hqbuun414.ASM1.asm ONLINE ONLINE on hqbuun414 ora.hqbuun414.LISTENER_HQBUUN414.lsnr ONLINE ONLINE on hqbuun414 ora.hqbuun414.gsd ONLINE ONLINE on hqbuun414 ora.hqbuun414.ons ONLINE ONLINE on hqbuun414 ora.hqbuun414.vip ONLINE ONLINE on hqbuun414 ora.hqbuun415.ASM2.asm ONLINE ONLINE on hqbuun415 ora.hqbuun415.LISTENER_HQBUUN415.lsnr ONLINE ONLINE on hqbuun415 ora.hqbuun415.gsd ONLINE ONLINE on hqbuun415 ora.hqbuun415.ons ONLINE ONLINE on hqbuun415 ora.hqbuun415.vip ONLINE ONLINE on hqbuun415
Who is using the DB Link?
On the magic web I found this script at Jared Still’s blog. It allows to see which database links are being used and by whom. Information sometimes useful (needs to be run as SYSDBA):
-- who is querying via dblink?
-- Courtesy of Tom Kyte, via Mark Bobak
-- this script can be used at both ends of the database link
-- to match up which session on the remote database started
-- the local transaction
-- the GTXID will match for those sessions
-- just run the script on both databases
Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
decode(bitand(ksuseidl,11),
1,'ACTIVE',
0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
2,'SNIPED',
3,'SNIPED',
'KILLED'
),1,1
) "S",
substr(w.event,1,10) "WAITING"
from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where g.K2GTDXCB =t.ktcxbxba
and g.K2GTDSES=t.ktcxbses
and s.addr=g.K2GTDSES
and w.sid=s.indx
and s2.sid = w.sidCheck which Oracle PSU are installed
With latest version of OPatch:
$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep -i ‘DATABASE PSU’
or
set lines 255
col action_time for a40
col action for a8
col namespace for a7
col version for a11
col comments for a40
select substr(ACTION_TIME,1,40) action_time,substr(ACTION,1,8) action,substr(NAMESPACE,1,7) namespace,
substr(VERSION,1,11) version,ID,substr(COMMENTS,1,40) comments,BUNDLE_SERIES
from registry$history;
To install latest OPatch, after download (make sure you only have OPatch zip for the correct Oracle version: 10.2, 11.1, 11.2):
- mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.old
- unzip p6880880* -d $ORACLE_HOME
ORA-14450, Global temporary tables and on commit preserve rows
— Clean environment
drop table gtt;
drop table gtt
*
ERROR at line 1:
ORA-00942: table or view does not exist
— Global temporary – preserve rows
create global temporary table gtt ( x int ) on commit preserve rows;
Table created.
— Keeps information until the end of the user session
select duration from user_tables where table_name = ‘GTT’;
DURATION
—————
SYS$SESSION
— Insert one row
insert into gtt values(1);
1 row created.
— Creates a TO lock
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
SID TY ID1 ID2 LMODE
———- — ———- ———- ———-
4343 TO 403115 1 3
— Commit transaction
commit;
Commit complete.
— TO lock continues…
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
SID TY ID1 ID2 LMODE
———- — ———- ———- ———-
4343 TO 403115 1 3
— Any user that tries to change the table
— Will receive: ORA-14450: attempt to access a transactional temp table already in use
alter table gtt add (y int);
alter table gtt add (y int)
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
— Only after doing truncate
truncate table gtt;
Table truncated.
— TO lock disappears
select * from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
no rows selected
— With a global temporary table delete rows
drop table gtt;
Table dropped.
create global temporary table gtt ( x int ) on commit delete rows;
Table created.
— The duration is at transaction level
select duration from user_tables where table_name = ‘GTT’;
DURATION
—————
SYS$TRANSACTION
— Insert one row
insert into gtt values(1);
1 row created.
— Creates a TO lock
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
SID TY ID1 ID2 LMODE
———- — ———- ———- ———-
4343 TO 403116 1 3
— That disappears as soon as you commit the transaction
commit;
Commit complete.
select sid, type, id1, id2, lmode from v$lock where id1=(select object_id from all_objects where object_name=’GTT’);
no rows selected
— Clean up environment
drop table gtt;
Table dropped.
New Oracle GoldenGate 11.2
Oracle released few weeks ago the new version of GoldenGate 11.2.1.0.0. The biggest novelty is the “integrated capture mode”, meaning that it starts working together with the internal log miner functionality.
This means no extra configuration is needed when extracting from an Oracle RAC or using ASM.
Soon I’ll be testing this new feature for a client.
Meanwhile, I leave you with a snippet of the “Release Notes”, page 6.
New ‘My Oracle Support’
Since this weekend, the http://supporthtml.oracle.com is called just “Dashboard”. Previously (still seen on http://support.oracle.com) it was called “My Oracle Support | Dashboard”.
Since years that Oracle tries to improve their paid support website, with little success. From the time of Metalink, the search speed is still a big handicap. The usability and mainly the user interface do not get any improvement from version to version.
There is the “APEX” layout which I personally don’t like. Then the choice of colors, gradients and curved edges which are all out of fashion and with lack of contrast for today’s webpages standards.
Let’s say what improved in the last 7 or 8 years in the support Oracle website:
- we can see service request messages history while adding more content
- we can see the list of files already uploaded to a service request and download them if necessary
- the search results are a more intelligent (if there is no misspell).
However there is one thing that still I find incomprehensible: the lack of speed of the search engine.
Will it be there one time that a “Google like” (well, maybe the expectation is too high) search engine will be deployed at My Oracle Support?
Just to finish, a first points where the new “My Oracle Support” html version is behind the flash version:
- no highlight of service request on “customer working” status
And a point where there is an improvement:
- you can now resize the tables on the dashboard to see more “Service Requests” or more “News”.
Kill long running transactions
For instance the ones running for more than 12hours:
select ‘alter system kill session ”’||s.sid||’,’||s.serial#||”’ immediate;’,
s.osuser,s.machine,s.module, CEIL(24*(SYSDATE-t.START_DATE))
from v$session s, v$transaction t
WHERE S.SADDR=T.SES_ADDR
and CEIL(24*(SYSDATE-t.START_DATE))>12;