Oracle Streaming Synchronization Troubleshooting

Oracle Streaming Health checkup scripts

streams_hc_10GR2.sql
====================

prompt ++ OPEN STREAMS CAPTURE TRANSACTIONS ++
prompt
prompt +** Count **+
select streams_name, count(*) “Open Transactions”,sum(cumulative_message_count) “Total LCRs” from gv$streams_transaction where streams_type=’CAPTURE’ group by streams_name;
prompt
prompt +** Detail **+
select * from gv$streams_transaction where streams_type=’CAPTURE’ order by streams_name,first_message_number;

prompt ++ APPLY SPILLED TRANSACTIONS ++

col APPLY_NAME Head ‘Apply Name’
col txn_id HEAD ‘Transaction|ID’
col FIRST_SCN Head ‘SCN of First| Message in Txn’
col MESSAGE_COUNT Head ‘Count of |Messages in Txn’
col FIRST_MESSAGE_CREATE_TIME Head ‘First Message|Creation Time’
col SPILL_CREATION_TIME Head ‘ Spill |Creation Time’

select Apply_name,
xidusn||’.’||xidslt||’.’||xidsqn txn_id,
first_scn,
first_message_create_time,
message_count,
spill_creation_time
from dba_apply_SPILL_TXN;

prompt ++ MESSAGES IN BUFFER QUEUE ++
col QUEUE format a50 wrap
col “Message Count” format 9999999999999999 heading ‘Current Number of|Outstanding|Messages|in Queue’
col “Spilled Msgs” format 9999999999999999 heading ‘Current Number of|Spilled|Messages|in Queue’
col “TOtal Messages” format 9999999999999999 heading ‘Cumulative |Number| of Messages|in Queue’
col “Total Spilled Msgs” format 9999999999999999 heading ‘Cumulative Number|of Spilled|Messages|in Queue’
col “Expired_Msgs” heading ‘Current Number of|Expired|Messages|in Queue’

SELECT queue_schema||’.’||queue_name Queue,
startup_time,
num_msgs “Message Count”,
spill_msgs “Spilled Msgs”,
cnum_msgs “Total Messages”,
cspill_msgs “Total Spilled Msgs”,
expired_msgs
FROM gv$buffered_queues;

================================
streams_hc_11_2_0_2.sql
================================
prompt
prompt ++ OPEN STREAMS CAPTURE TRANSACTIONS ++
prompt

prompt +** Count **+
select streams_name, count(*) “Open Transactions”,sum(cumulative_message_count) “Total LCRs” from gv$streams_transaction where streams_type=’CAPTURE’ group by streams_name;

prompt
prompt +** Detail **+
select * from gv$streams_transaction where streams_type=’CAPTURE’ order by streams_name,first_message_number;

prompt ++ APPLY SPILLED TRANSACTIONS ++

col APPLY_NAME Head ‘Apply Name’
col txn_id HEAD ‘Transaction|ID’
col FIRST_SCN Head ‘SCN of First| Message in Txn’
col MESSAGE_COUNT Head ‘Count of |Messages in Txn’
col FIRST_MESSAGE_CREATE_TIME Head ‘First Message|Creation Time’
col SPILL_CREATION_TIME Head ‘ Spill |Creation Time’
col transaction_id Head ‘XStream|Txn ID’
col first_position Head ‘XStream|Position’

select Apply_name,
xidusn||’.’||xidslt||’.’||xidsqn txn_id,
first_scn,
first_message_create_time,
message_count,
spill_creation_time ,
first_position,
transaction_id
from dba_apply_SPILL_TXN;

MESSAGES IN BUFFER QUEUE
prompt Check the capture/apply statistics to determine if CCA is in effect for a specific queue
prompt
prompt
col QUEUE format a50 wrap
col “Message Count” format 9999999999999999 heading ‘Current Number of|Outstanding|Messages|in Queue’
col “Spilled Msgs” format 9999999999999999 heading ‘Current Number of|Spilled|Messages|in Queue’
col “TOtal Messages” format 9999999999999999 heading ‘Cumulative |Number| of Messages|in Queue’
col “Total Spilled Msgs” format 9999999999999999 heading ‘Cumulative Number|of Spilled|Messages|in Queue’
col “Expired_Msgs” heading ‘Current Number of|Expired|Messages|in Queue’

SELECT queue_schema||’.’||queue_name Queue,
startup_time,
num_msgs “Message Count”,
spill_msgs “Spilled Msgs”,
cnum_msgs “Total Messages”,
cspill_msgs “Total Spilled Msgs”,
expired_msgs
FROM gv$buffered_queues;

Oracle® Streams Concepts and Administration
11g Release 2 (11.2)

Troubleshooting Apply
http://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_trapply.htm#BABHBJHA

Troubleshooting Propagation
http://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_trprop.htm#BCEJCHJJ

Troubleshooting Implicit Capture
http://docs.oracle.com/cd/E11882_01/server.112/e17069/strms_trcapture.htm#CACCGFEH

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s