Oracle Notes

Sharing Oracle technology knowledge and experiences.

Archive for August, 2010

Log Buffer #198, A Carnival of the Vanities for DBAs

Posted by sdefilip on August 6, 2010

Welcome to Log Buffer, the weekly roundup of database industry news.  I’m guest hosting this week’s edition, Log Buffer #198[S D1] [S2] , normally published by Pythian.  If you want to host your own version of Log Buffer, reach out to the Log Buffer coordinator.

Lots of good “stuff” out there on the technical blogs this week so here are some interesting ones:

In “Kerry Osborne’s Oracle Blog”, Kerry discusses the use of SQL Profiles

which contain sets of hints.

The memory_target parameter, how it behaves and how it relates to other memory structures, is the topic of Steve Karam’s on his “Oracle Alchemist” blog.

On “Mark Rittman’s Oracle Weblog”, Mark Rittman informs us how we can now add incremental patches to an RPD within OBIEE 11GgR1, through the use of XML patch files.

Scalability and the Oracle conflicts of interest that can be brought about by managing it are illustrated by

Jonathan Lewis on Oracle Scratchpad.

Tanel Poder offers his article on Troubleshooting Exadata V2 Smart Scan Performance in his “Core IT for Geeks and Pros” blog.

In a series of posts on the topic of the SQL Server 2008 Optimiser: Constructing a Plan, Part 1 & Part 2, on the “SQL Server Blogspot on the Web”, Paul White provides detail on how to get SQL code and queries to perform at their best.

Simon Sabin discusses Creating a Unique Constraint on a Column That Allows Nulls on “Simon’s SQL Blog”.

Distributed Transactions and Two-Phase Commit in PostgreSQL is the subject of Joshua Tolley’s informative post on “The Endpoint” blog.

Dimitri Fontaine talks about Querying the Catalog to plan an upgrade, on Planet PostgreSQL.  In more news, PostgreSQL 9.0 beta 4 is out and ready for testing.

On the “MySQL Performance Blog”,  Baron Shwartz cautions Why You Can’t Rely on a Replica for Disaster Recovery.


[S D1]http://wp.me/pYWgn-1o

Posted in Tips, Uncategorized | 1 Comment »

Using Oracle GoldenGate’s Logdump Utility

Posted by sdefilip on August 5, 2010

USING ORACLE GOLDENGATE’S LOGDUMP UTILITY

A recent client had a problem which required the use of the OGG Logdump utility to verify whether a particular table row had been extracted and placed in the Replicat trail file for replication to the target.  In this post, I will outline the steps that were used to find the answer.

1.   Begin logdump session:

            $ logdump

          GoldenGate Log File Dump Utility

          Version 10.4.0.12 Build 001

          Copyright (C) GoldenGate Software, Inc. 1995-2009.

          The GoldenGate software may include other code written by third parties;

          details regarding such third party code, including applicable copyright,

          legal and licensing notices are available at http://support.goldengate.com.

                   

2.    Open Replicat Trail File

 

Logdump 1>open ./dirdat/ad000053

Current LogTrail is /shared/oradb/GoldenGate/pnet01d/dirdat/ad000053

Logdump 2 >ghdr on       <–shows record header info about transaction

Logdump 3 >detail data     <— adds  hex and ASCII data values to the

                                                        column information                       

Logdump 4 >next            <—  moves to the first record; advances by one

                                                    record

 

2010/08/04 21:22:18.134.541 FileHeader           Len   928 RBA 0

Name: *FileHeader*

 3000 01ad 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0…0…GG..TL..1…

 0002 3200 0004 ffff fffd 3300 0008 02f1 b33c d650        | ..2…….3……<.P

 840d 3400 0031 002f 7572 693a 7473 7332 6130 3137 | ..4..1./uri:tss2a017

 6e33 3a3a 7368 6172 6564 3a6f 7261 6462 3a47 6f6c  |::shared:oradb:Gol

 6465 6e47 6174 653a 706e 6574 3031 6436 0000 1300 |denGate:anondb02

 112e 2f64 6972 6461 742f 6164 3030 3030 3533 3700 |…/dirdat/ad0000537

 0001 0138 0000 0400 0000 3539 0000 0800 0000 001d | …8……59……..

(above is header data in hex and char)

2010/08/04 22:36:23.000.000 Insert               Len   456 RBA 6608  <–this is

                                                                                                                                an insert

                                                                                                                                 at this rba

Name: FIN_AUDIT.FIN_GL_ATTR      <— source object

 

Logdump 5>next      <— shows next record which contains continuing header

                                            info; “next’ or ‘n’ also  scans through records in

                                            sequential order

___________________________________________________________________

Hdr-Ind    :     E  (x45)     Partition  :     .  (x00)

UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)

RecLength  :     0  (x0000)   IO Time    : 2010/08/04 21:22:17.611.797

IOType     :   151  (x97)     OrigNode   :     0  (x00)

TransInd   :     .  (x03)     FormatType :     R  (x52)

SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)

AuditRBA   :          0       AuditPos   : 0

Continued  :     N  (x00)     RecCount   :     0  (x00)

 

2010/08/04 21:22:17.611.797 RestartOK            Len     0 RBA 936

Name:

After  Image:                                             Partition 0   G  s

 

3.    Search for Timestamp in Trail File

 

Logdump 6 >sfts 2010-08-04       <–searches for the timestamp ‘2010-08-04’

Scan for timestamp >= 2010/08/04 04:00:00.000.000 GMT    <— timestamp

                                                                                                                          found

Column    41 (x0029), Len    31 (x001f)       <–column info with data

 0000 3230 3130 2d30 382d 3034 3a32 323a 3336 3a30 | ..2010-08-04:22:36:0

 372e 3839 3333 3139 3030 30                       | 7.893319000RecLength :9 (x0000)   IO Time    : 2010/08/04 21:22:17.611.797

 Since we were looking for any records that were inserted on 2010/08/04, we have found a valid entry in the Replicat trail file that verifies that the record has been placed in the file on the target side by the OGG Collector component.

 A few other of my favorite, very useful  Logdump commands are:

 >filter include filename FIN_AUDIT.FIN_GL_ATTR   

     Which will filter out all records that do not contain the table name

     specified and narrow down the set of records that subsequent commands

     will operate on until a “>filter clear” is issued

>pos FIRST

      Which will go to the first record in the file

 >pos <rba>

       Which will go to a specific rba in the trail file

 >log to <filename>.txt

        Which will log your Logdump session

There are several other useful commands for examining trail files or redo logs.  See the Oracle GoldenGate Troubleshooting Guide  for a complete list.

 

 

Posted in Data Integration, Data Warehouse, Golden Gate, Oracle GoldenGate, Oracle Troubleshooting, Replication, Tips | Tagged: , , , , | 1 Comment »