My New Art Projects

                                                           My Abstract Paintings.. 

Painting was my favorite.. But stopped it as soon as I entered my 10th Standard and until now, I have never touched the colors, paint brush or canvas. After a long time, I recently started working on it but soon realized that the skill for holding paint brush became pretty difficult for me. Meanwhile i am working on that, I started on the Abstract painting with Acrylics and I am loving it..

Bright emotion of Lava 

(Inspired by Brigitte König)

Ganesh on my Abstract Paint..

CouchBase Installation on Ubuntu

Couchbase Installation..

Couchbase installation is pretty straight forward.

go to http://www.couchbase.com/download and based on your operating system & Config, download the required version - Typically around 150 MB of size.
you can download it to the machine using sftp as shown below.

Note : for Red Hat Linux Systems, thing to consider is : THP (Transparent huge pages) should be disabled which is recommend by the couch base.
/* Check Status

You can check the current status of THP with the following command:

cat /sys/kernel/mm/*transparent_hugepage/enabled

Output will resemble the following, with the current status marked in [brackets]:

[always] madvise never

 Always indicates that THP will be used for all allocations, and Couchbase recommends disabling THP in this instance. Note: Older kernels may only show the always and never options. 

You can check the current status of THP defrag with the following command:

cat /sys/kernel/mm/*transparent_hugepage/defrag

Output will resemble the following, where always is specified, meaning that THP defrag is always enabled:

[always] never

Note: some distributions name the flag transparent_hugepage, others redhat_transparent_hugepage, hence the wildcard). */ 

For more info, please visit : http://support.couchbase.com/entries/22160929-Transparent-Huge-Pages

UbantoAz:Downloads raj$ sftp 10.64.211.111

rmattewada@10.64.211.238's password: 

Connected to 10.64.211.111.

sftp> cd /tmp

sftp> pwd

Remote working directory: /tmp

sftp> put couchbase-server-enterprise_2.5.1_x86_64.deb  .

Uploading couchbase-server-enterprise_2.5.1_x86_64.deb to /tmp/./couchbase-server-enterprise_2.5.1_x86_64.deb

couchbase-server-enterprise_2.5.1_x86_64.deb                                                                                    100%  134MB   5.6MB/s   00:24    

sftp> ls

couchbase-server-enterprise_2.5.1_x86_64.deb          

Once the copy is done, you can follow below as root user:
Note that in Ubuntu, you have Debian as package manager, where as in RHEL, you will see yum.

root@csshtest-6001:/opt# pwd

/opt

root@csshtest-6001:/opt# cp /tmp/couchbase-server-enterprise_2.5.1_x86_64.deb .

root@csshtest-6001:/opt# uname -a

Linux csshtest-6001 3.13.0-24-generic #47-Ubuntu SMP Fri May 2 23:30:00 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

root@csshtest-6001:/opt# #For Ubuntu version 12.04, you need to install a specific OpenSSL dependency by running:

root@csshtest-6001:/opt# id

uid=0(root) gid=0(root) groups=0(root)

root@csshtest-6001:/opt# apt-get install libssl0.9.8

Reading package lists... Done

Building dependency tree       

Reading state information... Done

The following NEW packages will be installed:

  libssl0.9.8

0 upgraded, 1 newly installed, 0 to remove and 87 not upgraded.

Need to get 692 kB of archives.

After this operation, 2,445 kB of additional disk space will be used.

Get:1 http://installsvc.vip/mirrors/archive.ubuntu.com/ubuntu/ trusty-updates/universe libssl0.9.8 amd64 0.9.8o-7ubuntu3.2.14.04.1 [692 kB]

Fetched 692 kB in 0s (4,135 kB/s)

Preconfiguring packages ...

Selecting previously unselected package libssl0.9.8:amd64.

(Reading database ... 56731 files and directories currently installed.)

Preparing to unpack .../libssl0.9.8_0.9.8o-7ubuntu3.2.14.04.1_amd64.deb ...

Unpacking libssl0.9.8:amd64 (0.9.8o-7ubuntu3.2.14.04.1) ...

Setting up libssl0.9.8:amd64 (0.9.8o-7ubuntu3.2.14.04.1) ...

Processing triggers for libc-bin (2.19-0ubuntu6) ...

root@csshtest-6001:/opt# 

root@csshtest-6001:/opt# #The Ubuntu Couchbase installation uses the DEB package. To install, use the dpkg command-line tool using the DEB file that you downloaded. The following example uses sudo which will require root-access to allow installation:

root@csshtest-6001:/opt# 

root@csshtest-6001:/opt# mkdir couchbase

root@csshtest-6001:/opt# cd couchbase/

root@csshtest-6001:/opt/couchbase# cp ../couchbase

couchbase/                                    couchbase-server-enterprise_2.5.1_x86_64.deb  

root@csshtest-6001:/opt/couchbase# cp ../couchbase-server-enterprise_2.5.1_x86_64.deb  .

root@csshtest-6001:/opt/couchbase# 

root@csshtest-6001:/opt/couchbase# dpkg -i couchbase-server-enterprise_2.5.1_x86_64.deb 

Selecting previously unselected package couchbase-server.

(Reading database ... 56755 files and directories currently installed.)

Preparing to unpack couchbase-server-enterprise_2.5.1_x86_64.deb ...

libssl1* is installed. Continue installing

Minimum RAM required  : 4 GB

System RAM configured : 1017948 kB

Minimum number of processors required : 4 cores

Number of processors on the system    : 1 cores

Unpacking couchbase-server (2.5.1) ...

Setting up couchbase-server (2.5.1) ...

 * Started couchbase-server

You have successfully installed Couchbase Server.

Please browse to http://csshtest-6001:8091/ to configure your server.

Please refer to http://couchbase.com for additional resources.

Please note that you have to update your firewall configuration to

allow connections to the following ports: 11211, 11210, 11209, 4369,

8091, 8092, 18091, 18092, 11214, 11215 and from 21100 to 21299.

By using this software you agree to the End User License Agreement.

See /opt/couchbase/LICENSE.txt.

Processing triggers for ureadahead (0.100.0-16) ...

root@csshtest-6001:/opt/couchbase# 

Now, open a browser and type in : http://ip_address:8091
you will get the set up prompt, click on that and you will see

decide on per server Ram Quota - Typically 75% of the total available RAM or
80% of the total available RAM if you are using XDCR (Cross Data Center Replication)
Note: you can join a cluster immediately (if you are planning to do so) if you check that instead of starting as a new cluster.

If you want to play with the sample data, check in for available samples like Beer or gamesim.

Depending on your I/O Load, chose the reader/sriter workers
Do not enable Flush if you are using this for production.
decide on per node ram Quota for bucket and allocate one.

pass through the next step and at step 5, provide username, password and you are done.

You can follow the same for all the servers in your cluster and bring them up. Then, you can add the nodes to a cluster.

Observations:

Observations:
1. when there are 3 nodes in a cluster,
if 2 nodes are down and another node is added, but in pending balance state,
and in that state, if that server server goes down, the admin console says:
Servers Down: 2
Servers Pending Rebalance: 1
where as i think it should say Servers Down: 3

2. when rebalance is in progress, if we click on the stop rebalance, the operation would not stop
though it says "rebalance stopped by janitar"
to verify that, in the server nodes tab, go to pending rebalance and you will not notice anything pending.

NoSql - Mongo DB Free Online Course - M102 Mongo DBA

Hi Viewers,
Mongo is offering free online courses and a course completion certificate. If you are eager to learn about the nosql databases, its a good opportunity for you all. You can go to https://university.mongodb.com and register for the course you want. Not only DBA courses, but also Developer courses are being offered. 

I got my course completed recently. You will need to follow the online classes, do quizzes, submit your homework in time and there is a final exam. Once you are done with all these, you will be awarded with a course completion certificate.

Mine is below :)

If you have questions regarding the course, I can help you..

Thanks,

-Raj

Strage behavior on renaming an IOT table - oracle 11g - 11.2.0.2.0 "truncating (as requested) before column"

I have created a heap table,
then an IOT with CTAS from above table
and then altered the IOT to rename it.

as soon as i ran a select on dba_tables, i noticed the Truncating commands and was little scared.
Since the issue is not reproducible, didn't open any SR with Oracle and still wonder why that strange output was shown. If anybody noticed the similar behavior and got the reason, please let me know.


19:56:51 SQL> conn raj/***
Connected.
19:56:54 SQL> CREATE TABLE test123
19:56:55   2     ( asd    NUMBER(6)
19:56:55   3     , first_name     VARCHAR2(20)
19:56:55   4     , CONSTRAINT     test123_pk
19:56:55   5                      PRIMARY KEY (asd)
19:56:55   6     );

Table created.

Elapsed: 00:00:00.60

19:57:09 SQL> create table test321 (asd, first_name,
19:57:09   2  constraint new_pk
19:57:09   3  primary key (asd))
19:57:09   4  organization index
19:57:09   5  as select * from test123;

Table created.

Elapsed: 00:00:00.03
19:57:49 SQL>
19:57:54 SQL> alter table test321 rename to testraj;

Table altered.

Elapsed: 00:00:00.96
19:59:31 SQL> select * from dba_tables where table_name ='TESTRAJ';
truncating (as requested) before column MAX_TRANS

truncating (as requested) before column INITIAL_EXTENT

truncating (as requested) before column NEXT_EXTENT

truncating (as requested) before column MIN_EXTENTS

truncating (as requested) before column MAX_EXTENTS

truncating (as requested) before column PCT_INCREASE

truncating (as requested) before column FREELISTS

truncating (as requested) before column FREELIST_GROUPS

truncating (as requested) before column NUM_ROWS

truncating (as requested) before column BLOCKS

truncating (as requested) before column EMPTY_BLOCKS

truncating (as requested) before column AVG_SPACE

truncating (as requested) before column CHAIN_CNT

truncating (as requested) before column AVG_ROW_LEN

truncating (as requested) before column AVG_SPACE_FREELIST_BLOCKS

truncating (as requested) before column NUM_FREELIST_BLOCKS

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated

rows will be truncated


OWNER      TABLE_NAME                     TABLESPACE_NAME CLUSTER_NAME                   IOT_NAME                       STATUS               PCT_FREE             PCT_USED            INI_TRANS LOG B D
---------- ------------------------------ --------------- ------------------------------ ------------------------------ -------- -------------------- -------------------- -------------------- --- - -
RAJ        TESTRAJ                                                                                                      VALID                       0                    0                    0     N

Elapsed: 00:00:00.36
20:00:25 SQL>

ORA-00600 when using EXPDP & IMPDP on a RAC Database.

When exporting metadata (without data) using EXPDP, things often proceed smoothly. However, when performing an IMPDP using the sqlfile mode, some Oracle RAC environments may trigger an ORA-00600 internal error. This is recognized by Oracle Support as a bug (no patch may yet exist), so caution is needed before running IMPDP in production.

The Scenario & Symptoms

• EXPDP command (metadata-only export) completes without issues.

• IMPDP with sqlfile mode fails with an internal error:

  ORA-00600: internal error code, arguments: [15851], [4], [4], [1], [3], …  
  

• The alert logs show that Oracle issues “ALTER SYSTEM SET SERVICE_NAMES” during the import attempt, introducing a generated SYS$SYS service name, e.g., SYS$SYS.KUPC$C_....

• The jobs DM00/ DW00 (Data Pump processes) may attempt operations that lead to the error trace.

• No lasting impact to the database is typically observed, but the import fails and the sqlfile is not created.

Sample Log Extract (from alert log)

ALTER SYSTEM SET service_names='SRV_JOB','SRV_ORCL','ORCL','SYS$SYS.KUPC$C_4_20130826105315.ORCL' SCOPE=MEMORY SID='ORCL_4';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_4_20130826105315.ORCL','SRV_JOB','SRV_ORCL','ORCL',... SCOPE=MEMORY SID='ORCL_4';
DM00 started ...
DW00 started ...
ORA-00600: internal error code, arguments: [15851], [4], [4], [1], [3], …

It’s noted that this may be triggered specifically when running IMPDP in a RAC context, due to internal handling of services and metadata injection.

Possible Root Causes (RCA) & Mechanisms

1. Dynamic Service Registration / Modification
   Data Pump (especially in RAC) may issue or adjust SERVICE_NAMES entries to manage worker processes across instances. The generation of internal service names (SYS$SYS.*) might cause inconsistencies or unexpected internal state transitions in the process.

2. Bug in Data Pump / RAC Interaction
   The presence of [15851] in the ORA-00600 arguments suggests a known issue in the Oracle kernel or Data Pump internals, where a corner-case in metadata import on RAC nodes triggers an undesired internal assertion or state.

3. Incomplete Statistics / Missing Fixed Object Stats
   As you observed, running DBMS_STATS.GATHER_FIXED_OBJECTS_STATS() is sometimes proposed as a workaround, but that alone may not fully mask the underlying bug or prevent the failure in all environments.

Workaround & Mitigation Strategies

• Execute:

  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
  

  This collects statistics on fixed dictionary objects, which might prevent certain internal errors. However, in your tests, this did not reliably mitigate the issue.

• Use metadata-only EXPDP / IMPDP without sqlfile mode when feasible.

• Instead of sqlfile, use DIRECT import of metadata (i.e. let IMPDP apply changes rather than generating script), while monitoring carefully in lower environments first.

• Validate that all RAC instances are stable, with consistent service configurations, before initiating the import.

• If possible, run the import on a single instance (non-RAC) environment, generate the SQL file there, and then apply it in the RAC environment manually after validation.

• Contact Oracle Support, referencing the internal error arguments [15851] and your database version/RAC configuration, to see if they have a patch or workaround for your specific version.

Best Practices & Considerations

• Always test Data Pump operations in lower environments before running in production, especially on RAC architectures.

• Maintain up-to-date Oracle patches and PSU bundles, because some internal bugs may be fixed in patch sets.

• Collect full diagnostics: run ADRCTL or ADRCI to collect the incident dump, trace files, and metadata, and provide them to Oracle Support:

  Use ADRCI or Support Workbench to package the incident (see Note 411.1 on MOS).
  

• Monitor your trace directory size limits—errors like ORA-48913 (writing to trace file failed due to size limit) suggest your trace file quotas may have been exceeded.

• Archive trace files and incidents promptly to avoid file system overflow.

• Validate service name consistency across RAC nodes, and avoid unnecessary dynamic service additions unless absolutely needed.

Summary

• The ORA-00600 [15851] error during IMPDP … SQLFILE in a RAC environment is a known internal issue triggered by service modifications in Data Pump.

• While GATHER_FIXED_OBJECTS_STATS may sometimes alleviate the issue, it is not a guaranteed fix.

• Use alternative import strategies (direct metadata import, non-RAC staging, careful scripting) when possible, and involve Oracle Support with full diagnostics if needed.

Definition of a Table Using defgen Utility (Oracle GoldenGate)

Definition of a table using defgen Utility (oracle golden gate) - simple script to automatically get your table definitions if you are using UserExits or if you are relying on the definition file rather than the replicat parameter file.. Do not forget to update your table definitions when the source table has been modified.

Important Note: Always update your definition files (DEFSFILE) when the source table structure changes, to avoid replicat failures. Test in non-production environments first.
Btw, edit the home, golden gate locations accordingly to suit your environment..

Why Use defgen?

• In certain GoldenGate replication setups, especially when using DEFSFILE or UserExits, the replicat process expects table definitions externally instead of deriving them from the parameter file.

• defgen helps you generate those definitions (metadata) automatically from your Oracle source database (via queries on DBA_TABLES) into a .def file.

• This approach helps ensure consistency, reduce manual errors, and support dynamic table changes.

Script: def_gen.sh

#!/bin/bash

# ---------------------------------------------------------------------

# def_gen.sh — Generate GoldenGate DEFSFILE (table definitions) automatically

# Usage: ./def_gen.sh

# Author: Raj 

# Adjust below variables to your environment (Oracle home, GoldenGate paths, owners)

# ---------------------------------------------------------------------

# Adjust these to suit your system

ORACLE_BASE="/u01/orcl"

GG_HOME="/u01/orcl/goldengate/111"

# Source your Oracle environment (ORACLE_HOME, PATH, etc.)

. ${ORACLE_BASE}/.profile

cd ${ORACLE_BASE}

cd ${ORACLE_BASE}/gg

# Move old .obey files to logs directory (backup/archive)

if [ ! -d logs ]; then

  mkdir logs

fi

mv *.obey logs/ 2>/dev/null

TIMESTAMP=$(date +"%m-%d-%y-%H-%M-%S")

OBEY_FILE="${ORACLE_BASE}/gg/defgen_${TIMESTAMP}.obey"

DEFINITION_FILE="${ORACLE_BASE}/gg/defgen_${TIMESTAMP}.def"

# Use SQL*Plus to generate the .obey file (instructions for defgen)

sqlplus -s / as sysdba <<EOF

SET ECHO OFF

SET HEAD OFF

SET TRIMSPOOL ON

SET FEEDBACK OFF

SPOOL ${OBEY_FILE}

PROMPT --DEFSFILE

PROMPT DEFSFILE ${DEFINITION_FILE},PURGE

PROMPT USERID ggs, PASSWORD <your_password_here>

SELECT 'TABLE ' || OWNER || '.' || TABLE_NAME || ';'

  FROM DBA_TABLES

 WHERE OWNER IN (

   'SCOTT',

   'HR'

   -- Add your schemas here; you can also filter by TABLE_NAME if desired

 );

SPOOL OFF

EOF

# Change directory to GoldenGate home

cd ${GG_HOME}

# Execute defgen with the generated obey file

# Alternatively: echo "obey ${OBEY_FILE}" | ./defgen

./defgen PARAMFILE ${OBEY_FILE}

# List the new definition files

ls -ltr ${ORACLE_BASE}/gg

Sample Output: .def File

When you run the script successfully, your .def output file will look something like this:

raj@localhost > cat defgen_05-18-13-02-24-59.def


* Definitions created/modified  2013-05-18 02:25
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*
*
Definition for table SCOTT.VALUES_TAB
Record length: 430
Syskey: 0
Columns: 5
ID       64     50        0  0  0 1 0     50     50     50 0 0 0 0 1    0 1 2
NAME     64    200       56  0  0 1 0    200    200      0 0 0 0 0 1    0 0 0
VALUE1   64     50      262  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
VALUE2   64     50      318  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
VALUE3   64     50      374  0  0 1 0     50     50     50 0 0 0 0 1    0 0 2
End of definition

Each line in the table describes a column’s metadata, with positional fields (Name, Data Type, Nullability, etc.) required by GoldenGate.

Best Practices (Based on Experience)

1. Dynamic Schema Lists
   Instead of hardcoding OWNER IN ( … ), you can generate the list of schemas dynamically (or via a control table) so you don’t need to edit the script when schemas change.

2. Filter by Table Name or Last DDL Time
   Optionally filter your SELECT by TABLE_NAME or by recent LAST_DDL_TIME to regenerate definitions only for recently modified tables.

3. Parameterize Passwords Securely
   Avoid putting plain-text passwords in the script. Use encrypted wallets, environment variables, or Oracle OS authentication for security.

4. Dry-Run Mode
   Introduce a -n or --dry-run flag to echo commands instead of executing defgen, for validation before production runs.

5. Logging & Alerts
   After running the script, capture return codes and send alerts (email) if defgen fails or if the .def file is empty.

6. Backup Existing Definition Files
   Before overwriting, archive prior .def files or maintain a version control system (e.g. Git) to track changes.

7. Schedule via Cron / Orchestration Engine
   Automate this script (e.g. once daily or before batch runs) using cron or orchestration tools like Ansible, making sure only one instance runs at a time.

8. Validation Post-Run
   After generating .def, run a validation check:

• Ensure non-empty .def file.

• Validate that it includes all expected tables.

• Optionally execute a test replicat process in a dev/test cluster.

Oracle Golden gate - Long running transactions while stopping an extract ?

Below is how you can find it out and kill if needed.


GGSCI (ggnode1) 7>send extract EXRAJ, showtrans duration 20 MIN

Sending showtrans request to EXTRACT EXRAJ ...

Oldest redo log files necessary to restart Extract are:

Redo Thread 1, Redo Log Sequence Number 74058, SCN 1586.3900894393 (6815719025849), RBA 104066064
Redo Thread 2, Redo Log Sequence Number 79546, SCN 1586.3894494112 (6815712625568), RBA 1458358288

------------------------------------------------------------
XID:                  1630.17.201166
Items:                1      
Extract:              EXRAJ  
Redo Thread:          1    
Start Time:           2013-05-10:21:43:50
SCN:                  1586.3900894393 (6815719025849)
Redo Seq:             74058
Redo RBA:             104066064        
Status:               Running          


------------------------------------------------------------
XID:                  9068.29.296116
Items:                1      
Extract:              EXRAJ  
Redo Thread:          2    
Start Time:           2013-05-10:21:13:50
SCN:                  1586.3894494112 (6815712625568)
Redo Seq:             79546
Redo RBA:             1458358288        
Status:               Running          


------------------------------------------------------------
XID:                  8805.6.296139
Items:                1      
Extract:              EXRAJ  
Redo Thread:          2    
Start Time:           2013-05-10:21:24:33
SCN:                  1586.3896755063 (6815714886519)
Redo Seq:             79547
Redo RBA:             791874576        
Status:               Running          



22:09:08 SQL> select * from gv$transaction where xidusn=8805;

   INST_ID ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS           START_TIME           START_SCNB START_SCNW START_UEXT START_UBAFIL
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------
START_UBABLK START_UBASQN START_UBAREC SES_ADDR               FLAG SPA REC NOU PTX NAME                                                                   PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN
------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------- ---------- ---------- ---------- ----------
PTX_XIDSLT PTX_XIDSQN     DSCN-B     DSCN-W  USED_UBLK  USED_UREC     LOG_IO     PHY_IO     CR_GET  CR_CHANGE START_DATE       DSCN_BASE  DSCN_WRAP  START_SCN DEPENDENT_SCN XID
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ---------- ---------- ---------- ------------- ----------------
PRV_XID          PTX_XID
---------------- ----------------
         2 0000001BFF3DEBD8       8805          6     296139        856     125452      27952          5 ACTIVE           05/10/13 21:24:33    3896755063       1586          4          856
      125452        27952            5 0000001C84587F40       7683 NO  NO  NO  NO                                                                                  0          0          0          0
         0          0          0          0          1          1          5          0          0          0 10-MAY-13                0          0 6.8157E+12             0 22650006000484CB
0000000000000000 0000000000000000



22:09:38 SQL> select sid,serial#,event,machine,sql_id,seconds_in_wait,prev_sql_id,module,program,action from gv$session where taddr='0000001BFF3DEBD8';

  SID    SERIAL# EVENT                          MACHINE         SQL_ID        SECONDS_IN_WAIT PREV_SQL_ID   MODULE         PROGRAM
----- ---------- ------------------------------ --------------- ------------- --------------- ------------- -------------- ------------------------------------------------
ACTION
----------------------------------------------------------------
 9871        167 SQL*Net message from client    client07                               2710 7zwu0n8myp5vn occ-raj-jar occworker@client07 (TNS V1-V3)
                                                                                                         
20f669b057722


22:09:43 SQL> select
hash_value, address,
executions,buffer_gets, disk_reads,
round(buffer_gets/decode(executions, 0, 1, executions), 1) avg_gets,
round(disk_reads/decode(executions, 0, 1, executions), 1) avg_disk,
last_load_time,
module,
sql_fulltext
from v$sqlarea
where sql_id='&sql_id';

Enter value for sql_id: 7zwu0n8myp5vn

HASH_VALUE ADDRESS          EXECUTIONS BUFFER_GETS DISK_READS   AVG_GETS   AVG_DISK LAST_LOAD_TIME      MODULE
---------- ---------------- ---------- ----------- ---------- ---------- ---------- ------------------- -------------------
SQL_FULLTEXT
--------------------------------------------------------------------------------
 669685620 0000001A4E4E8840   16774570    43243144       5728        2.6          0 10-MAY-13           occ-raj-jarers
INSERT INTO user_info (uid, part_key, t_id, uname) VALUES (:uid, :part_key, :t_id) /*Truncated the sql*/


you will see the following error message in ggserr.log file

2013-05-10 22:15:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle):  send EXRAJ showtrans duration 45m.
2013-05-10 22:15:16  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Command received from GGSCI: showtrans duration 45m.
Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:20  WARNING OGG-01027  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Long Running Transaction: XID 8805.6.296139, Items 1, Extract EXRAJ, Redo Thread 2, SCN 1586.3896755063 (6815714886519), Redo Seq #79547, Redo RBA 791874576.
2013-05-10 22:15:26  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): send EXRAJ showtrans duration 90m.
2013-05-10 22:15:28  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, EXRAJ.prm:  Command received from GGSCI: showtrans duration 90m.


select logon_time,status,LAST_CALL_ET from gv$session where sid=9871 and inst_id=2;

LOGON_TIME          STATUS   LAST_CALL_ET
------------------- -------- ------------
05/10/2013 19:26:36 INACTIVE         3226

or you can use:


set echo on
set timing on
col sid format 999999
col serial# format 999999
alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';
select  t.start_time,t.status TSTATUS, s.status SSTATUS,s.sid, s.serial# ,s.machine , s.sql_id,s.prev_sql_id,s.process,t.XIDUSN||'.'||t.XIDSLOT||'.'||t.XIDSQN XID from gv$transaction t, gv$session s  where t.addr=s.taddr and t.inst_id=s.inst_id and t.start_date < (sysdate-1/142) order  by t.start_time;


-- if needed, you can go ahead and kill the sql (if it is not of a major impact)

alter system kill session '9871,167';