RE: Oracle LOGMNR on Oracle RAC problem

  • From: "Guang Mei" <GuangMei@xxxxxxx>
  • To: "John Hallas" <john.hallas@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Mar 2008 12:21:36 -0400

Yes, redo logs are in shared storage (called G drive), and can be seen
by both nodes.

 

Guang

 

________________________________

From: John Hallas [mailto:john.hallas@xxxxxxxxxx] 
Sent: Thursday, March 20, 2008 12:04 PM
To: Guang Mei; oracle-l@xxxxxxxxxxxxx
Subject: RE: Oracle LOGMNR on Oracle RAC problem

 

Are the redo logs on shared storage and readable from both nodes?

 

John

 

www.jhdba.wordpress.com

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guang Mei
Sent: 20 March 2008 15:43
To: oracle-l@xxxxxxxxxxxxx
Subject: Oracle LOGMNR on Oracle RAC problem

 

Hi,

We are attempting to capture changes made to a 2 node oracle RAC
database using the log miner. The problem we are having only occurs when
we are using Oracle RAC. We are successful when we do this on a
non-Oracle RAC database. In all cases we believe that we have
supplemental logging turned on. 

 

We are using JDBC and connect to the database as follows:

jdbc:oracle:thin:@(description=

    (address=(protocol=tcp)(host=devwin15)(port=1521))

    (address=(protocol=tcp)(host=devwin16)(port=1521))

    (LOAD_BALANCE=yes)

    (connect_data=(server=dedicated)(service_name=winracdb)))

 

We are initiating the logging as follows using a JDBC connection. The
connection:

 

{call SYS.DBMS_LOGMNR.START_LOGMNR(STARTTIME => SYSDATE, 

    OPTIONS   => SYS.DBMS_LOGMNR.CONTINUOUS_MINE+

         SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+

         SYS.DBMS_LOGMNR.COMMITTED_DATA_ONLY ) }

 

We then read the data using a prepared statement with a fetch size of 1
using the following query:

 

SELECT SCN, COMMIT_TIMESTAMP, TABLE_NAME, SQL_REDO, CSF, OPERATION 

    FROM V$LOGMNR_CONTENTS 

    WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME IN ( 'CSM_PARAMETER',
'TS_ORDER' )

        AND SEG_OWNER = 'ORADEV91'

        AND OPERATION IN( 'UPDATE', 'DELETE', 'INSERT' )

 

To test the functionality I then update the CSM_PARAMETER table from
each of the hosts in the two node oracle RAC cluster.

 

When I modify the data from a connection to devwin15, it appears in the
result set returned by the select from v$logmnr_contents.

When I modify the data from a connection to devwin16, no entries appear
in the result set.

 

How can I make this work regardless of which host the connection has
been made to?

 

Other related posts: