crs_stat and Oracle cluster resources status

This entry was posted in Oracle RAC on by

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?

This entry was posted in DB Links Oracle on by

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.sid

Check which Oracle PSU are installed

This entry was posted in OPatch Oracle Patchset Update PSU on by

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

This entry was posted in Oracle on by

— 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

This entry was posted in GoldenGate Oracle on by

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’

This entry was posted in My Oracle Support Oracle on by

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

This entry was posted in Oracle on by

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;

Ideas I would like to develop

This entry was posted in ideas Oracle Oracle 10g on by

– testing and documenting the apply of Oracle PSU by:
. clone a master patched Oracle Home
. attach new home to inventory
. move existing DBs to use the new Oracle Home
. run post-PSU operations on each DB (catpsu.sql, etc)

Advantages:
– minimize downtime
– automatize patching

GoldenGate and invalid dates

This entry was posted in GoldenGate Oracle Oracle 10g on by

GoldenGate is a powerful tool bought by Oracle to replace the “Oracle Streams”. It allows heterogeneous replication of data and works just fine almost out-of-the-box.

My client uses it to make a cache copy of data a kind of active standby while running on Oracle 10.2.

Today I had to fight for a problem that comes from the application, which seldom writes to the source database dates in a invalid format, with an year 0 (Oracle should not accept, but the year got in, we are still investigating how).

This makes the GoldenGate replication process to abend, as the write on the target database fails with an “ORA-01841: (full) year must be between -4713 and +9999, and not be 0”.

For the workaround, using GoldenGate MAP function, I managed to transform the dates like the following:
Source: 0000-12-09:08:11:20
Target: 0001-12-09:08:11:20

MAP NESSOFT.NEXT_ORD, TARGET NESSOFT_NL.NEXT_ORD , &
COLMAP ( USEDEFAULTS,
EXPIRE = @IF ( @STRCMP(@STREXT(EXPIRE,1,4),"0000") = 0, @STRCAT("0001-",@STREXT(EXPIRE,6,18)),EXPIRE)
);

This allowed to me also to discover the discard file (.dsc) on the dirrtp folder of GoldenGate, where you can see the output of the MAP function, useful for the debug.