Tuesday, May 5, 2015

Oracle Streams - Troubleshooting / Commands / Scripts

Oracle Streams

1-  Check Apply Parameters 

column parameter format a30
column value format a20
column apply_name format a20
set verify off
select apply_name, parameter,value from dba_apply_parameters where apply_name ='&&APPLY_NAME';

set verify on

PROMPT
PROMPT Listing database events currently set:
PROMPT
column value format a60
select inst_id, value from gv$parameter where name = 'event';

2- Chek Apply 

set heading on

COLUMN APPLY_NAME FORMAT A25
COLUMN STATE HEADING 'Apply Srv State' FORMAT A16
COLUMN msg_number FORMAT 9999999999999999
COLUMN dq_time format a6
COLUMN Status format a10

set linesize 80


SELECT r.APPLY_NAME
  ,ap.status || ' n' || srv.inst_id  STATUS
  , DECODE(srv.state, 'EXECUTE TRANSACTION','EXECUTE TXN', srv.state) STATE
  ,r.DEQUEUED_MESSAGE_NUMBER MSG_NUMBER
  ,to_char(r.DEQUEUE_TIME,'HH24:MI') TIME
FROM gV$STREAMS_APPLY_READER r, DBA_APPLY ap, gV$STREAMS_APPLY_SERVER srv
WHERE r.inst_id=srv.inst_id
AND r.APPLY_NAME = ap.APPLY_NAME
AND r.APPLY_NAME = srv.APPLY_NAME
UNION
SELECT apply_name
   ,status
   , '(disabled)' state
   , NULL msg_number
   , NULL DQ_TIME 
FROM DBA_APPLY
WHERE status != 'ENABLED'


3- List Log Miner Check Points 

PROMPT This query can take some time to run if the LOGMNR_RESTART_CKPT$ table is large

select to_char(trunc(scn_to_timestamp(CKPT_SCN), 'HH'), 'DD-MON HH24:MI') CHKPT_SUM_HR, count(*)
from system.logmnr_restart_ckpt$
group by to_char(trunc(scn_to_timestamp(CKPT_SCN), 'HH'), 'DD-MON HH24:MI')
order by to_char(trunc(scn_to_timestamp(CKPT_SCN), 'HH'), 'DD-MON HH24:MI');


4- List Capture Rules : 

column streams_name format a25
column obj_name format a80
column rule_name format a20
set pagesize 120

  select DISTINCT(rule.streams_name || ' ' || rule.schema_name || '.' || rule.object_name || ' - ' || conf.resolution_column) obj_name
   from dba_streams_rules rule, dba_apply_conflict_columns conf
   where rule.object_name = conf.object_name (+)
   and rule.schema_name = conf.object_owner (+)
   and rule.streams_name like '%CAPTURE%'
   order by 1;

PROMPT
PROMPT Orphaned Rules:
PROMPT ===============

column RULE_CONDITION format a40
set long 1000
set pagesize 1000
select RULE_NAME,RULE_CONDITION
from  dba_streams_rules
where streams_type='CAPTURE'
and schema_name is NULL
and object_name is NULL;

set pagesize 120


5- List Capture Apply Handler 


  set linesize 150;
  set pagesize 80;

select DISTINCT('CaptureTableRule:' || rule.schema_name || '.' || rule.object_name || '   ConfResCol:' || conf.resolution_column || '   DMLHdlr:' || REPLACE(hdlr.user_procedure,'"','') )  TableRules
   from dba_streams_rules rule
   , dba_apply_conflict_columns conf
   , dba_apply_dml_handlers hdlr
   where rule.object_name = conf.object_name (+)
   and rule.schema_name = conf.object_owner (+)
   and rule.object_name = hdlr.object_name (+)
   and rule.schema_name = hdlr.object_owner (+)
   and rule.streams_name like '%CAPTURE%'
   order by 1;

6- Check Queues 


column QUEUE_NAME format a30

select QUEUE_NAME || ' n' || inst_id QUEUE_NAME,NUM_MSGS,SPILL_MSGS from gv$buffered_queues;



7- Check Propagation Errors 

set linesize 100;
set feedback off;
column destination format a25;
column last_error_msg format a55;

select destination,last_error_date,last_error_time
, last_error_msg
from dba_queue_schedules
/
set linesize 80
set feedback on



8- Check Propagation Configuration 

set verify off
PROMPT
PROMPT Current Configured Propagations:
set heading off
set linesize 120
select PROPAGATION_NAME || ' SourceQ: ' || SOURCE_QUEUE_NAME || ' DestLink: ' || DESTINATION_DBLINK || ' DestQ: ' || DESTINATION_QUEUE_NAME
from dba_propagation;
set heading on 
set linesize 120


9- Check Log Miner Chkpt 


set linesize 120
column session_name format a20
column oldest_Restart_SCN format 9999999999999
column oldest_Restart_DATE format a19
column newest_Restart_SCN format 9999999999999
column newest_Restart_DATE format a19


--SELECT s.session_name,min(r.ckpt_scn) oldest_Restart_SCN
--,to_char(scn_to_timestamp(min(r.ckpt_scn)), 'DD-MON-YY HH24:MI') oldest_Restart_DATE
--, max(r.ckpt_scn) newest_Restart_SCN
--,to_char(scn_to_timestamp(max(r.ckpt_scn)), 'DD-MON-YY HH24:MI') newest_Restart_DATE
--from system.logmnr_restart_ckpt$ r, system.logmnr_session$ s
--where s.session# = r.session#
--group by s.session_name;

column capture_name format a20
column oldest_capt_scn format 99999999999999999
column oldest_capt_SCNDATE format a20
column start_capt_SCNDATE format a20
column start_capt_SCN format 99999999999999999
column oldest_needed_scn format 99999999999999999
column oldest_needed_SCNDATE format a22
column newest_capt_scn format 99999999999999999
column newest_ckpt_scn format 99999999999999999
column newest_ckpt_DATE format a17

PROMPT
PROMPT SCN Values
PROMPT ==========
PROMPT Capture Restart SCNS:     (FIRST_SCN)    (REQUIRED_CHKPNT_SCN)   (START_SCN)     (MAX_CHKPNT_SCN)
select capture_name
, first_scn oldest_capt_scn
, required_checkpoint_scn oldest_needed_scn
, start_scn start_capt_scn
, max_checkpoint_scn newest_ckpt_SCN
from dba_capture;

PROMPT
PROMPT Date-Time Values
PROMPT ================
PROMPT Capture Restart SCNS:   (FIRST_SCN)       (REQUIRED_CHKPNT_SCN)   (START_SCN)        (MAX_CHKPNT_SCN)
select capture_name
--, logminer_id
--, first_scn oldest_capt_scn
, to_char(scn_to_timestamp(first_scn), 'DD-MON-YY HH24:MI') oldest_capt_SCNDATE
--, required_checkpoint_scn oldest_needed_scn
, to_char(scn_to_timestamp(required_checkpoint_scn), 'DD-MON-YY HH24:MI') oldest_needed_SCNDATE
, to_char(scn_to_timestamp(start_scn), 'DD-MON-YY HH24:MI') start_capt_SCNDATE
--, max_checkpoint_scn newest_capt_scn
, to_char(scn_to_timestamp(max_checkpoint_scn), 'DD-MON-YY HH24:MI') newest_ckpt_DATE
from dba_capture;


10 - Check Capture Wait 

column capture_name format a25
column process format a7
column state format a20
column event format a60
column sid_serial format a10
set linesize 120

select c.capture_name || ' n' || c.inst_id capture_name, c.sid ||',' || s.serial# SID_SERIAL
--, c.state 
  , s.process, s.event || ' p1:' || w.p1 || ' p2:' || w.p2 event
from gv$streams_capture c, gv$session s, gv$session_wait w
where s.inst_id = c.inst_id
and s.inst_id = w.inst_id
and s.sid = c.sid
and s.sid = w.sid
/



11- Check Capture Transactions : 

set linesize 120

column sid_serial format a14
column username format a12
column osuser format a12
column machine format a15
column XIDUSN_XIDSLT_XIDSQN format a15


select to_char(st.FIRST_MESSAGE_TIME, 'DD-MON-YY HH24:MI')
, st.xidusn || '.' || st.xidslt || '.' || t.xidsqn XIDUSN_XIDSLT_XIDSQN
--, st.xidslt
--, t.ses_addr 
--, s.inst_id
, s.sid || ',' || s.serial# || ' @n' || s.inst_id sid_serial
, s.username
,s.osuser
,s.MACHINE
,st.cumulative_message_count C_MESSAGE_COUNT
from gv$transaction t, gv$session s, gv$streams_transaction st
where t.inst_id = s.inst_id
and t.inst_id = st.inst_id
and s.saddr=t.ses_addr
and t.XIDUSN = st.XIDUSN 
and t.XIDSLOT = st.XIDSLT
and st.streams_type = 'CAPTURE';
set linesize 80

PROMPT
PROMPT NOTE: Ignore Transaction Syntax:
PROMPT
PROMPT   exec dbms_capture_adm.set_parameter('<CAPT_NAME>', '_ignore_transaction', '<XIDUSN.XIDSLT.XIDSQN>');
PROMPT


12- Check Capture Processes 

set linesize 90

column capture_name format a28
column role format a14
column sid_serial format a10
column os_process format a26

select c.capture_name || ' n' || v.inst_id CAPTURE_NAME
       , 'capture' ROLE
       , v.sid || ',' || v.serial# SID_SERIAL
       , REPLACE(p.program,'oracle@','') || ': ' || p.spid OS_PROCESS
from dba_capture c, gv$streams_capture v, gv$process p, gv$session s
where c.capture_name = v.capture_name 
and   s.sid=v.sid and s.serial#=v.serial# and p.ADDR=s.PADDR 
and   v.inst_id=p.inst_id and v.inst_id=s.inst_id
UNION
select v.capture_name || ' n' || v.inst_id CAPTURE_NAME
       , 'lgmnr ' || l.role
       ,l.sid || ',' || l.serial# SID_SERIAL
       , REPLACE(p.program,'oracle@','') || ': ' || p.spid OS_PROCESS
from gv$logmnr_process l, gv$streams_capture v, gv$process p, gv$session s
where   s.sid=l.sid and s.serial#=l.serial# and p.ADDR=s.PADDR
and   v.logminer_id = l.session_id
and   l.inst_id=p.inst_id and l.inst_id=s.inst_id
and   v.inst_id=p.inst_id and v.inst_id=s.inst_id
and l.role != v.capture_name
order by 1,2 
/


13- Check Capture Enqueue Time 

select capture_name, scn_to_timestamp(last_enqueued_scn) from dba_capture
/


14- Check Capture 


set linesize 90
column capture_name format a25
column status format a27
column status_change_time format a15
column capture_time format a15
column capture_msg format 99999999999999999
column start_scn format 99999999999999999999
column first_scn format 99999999999999999999
column captured_scn format 9999999999999999999

select c.capture_name
     , c.status || ' - ' || DECODE(v.state,'DICTIONARY INITIALIZATION','DICT. INIT','CAPTURING CHANGES','CAPT CHANGES','ENQUEUING MESSAGE','ENQUEUING MSG','WAITING FOR REDO','WAIT FOR REDO','PAUSED FOR FLOW CONTROL','PAUSE FLOW CTRL','DICTIONARY INITIALIZATION','DICTIONARY INIT',v.state) || ' n' || inst_id STATUS 
 --  , to_char(c.status_change_time,'DD-MON-YY HH24:MI') STATUS_CHANGE_TIME
     , v.capture_message_number CAPTURE_MSG
     , to_char(v.capture_time,'HH24:MI') TIME
from dba_capture c, gv$streams_capture v
where c.capture_name = v.capture_name (+)
/

set linesize 80


15- Check Apply SQL 

SELECT t.SQL_TEXT
FROM V$SESSION s, V$SQLTEXT t, V$STREAMS_APPLY_SERVER a
WHERE a.APPLY_NAME = '&APPLY_NAME'
AND s.SID = a.SID
AND s.SQL_ADDRESS = t.ADDRESS
AND s.SQL_HASH_VALUE = t.HASH_VALUE
ORDER BY PIECE;


16- Check Apply Processes


set linesize 90
set pagesize 20

column sid_serial format a10
column Apply_name format a28
column role format a14
column os_process format a26


select a.apply_name || ' n' || a.inst_id APPlY_NAME 
, 'coordinator' ROLE
, a.sid || ',' || a.serial# SID_SERIAL 
, REPLACE(p.program, 'oracle@','') || ': ' || p.spid OS_PROCESS
from gv$streams_apply_coordinator a , gv$process p, gv$session s
where s.sid=a.sid and s.serial#=a.serial# and p.ADDR=s.PADDR
and a.inst_id=p.inst_id and a.inst_id=s.inst_id
UNION 
select a.apply_name || ' n' || a.inst_id APPlY_NAME 
, 'server' ROLE
, a.sid || ',' || a.serial# SID_SERIAL 
, REPLACE(p.program, 'oracle@','') || ': ' || p.spid OS_PROCESS
from gv$streams_apply_server a , gv$process p, gv$session s
where s.sid=a.sid and s.serial#=a.serial# and p.ADDR=s.PADDR
and a.inst_id=p.inst_id and a.inst_id=s.inst_id
UNION 
select a.apply_name || ' n' || a.inst_id APPlY_NAME
, 'reader' ROLE
, a.sid || ',' || a.serial# SID_SERIAL
, REPLACE(p.program, 'oracle@','') || ': ' || p.spid OS_PROCESS
from gv$streams_apply_reader a , gv$process p, gv$session s
where s.sid=a.sid and s.serial#=a.serial# and p.ADDR=s.PADDR
and a.inst_id=p.inst_id and a.inst_id=s.inst_id
order by 1,2
/



17 - Check Apply Plan

SELECT p.OPERATION, p.OPTIONS, p.OBJECT_OWNER, p.OBJECT_NAME, p.COST
FROM V$SQL_PLAN p, V$SESSION s, V$STREAMS_APPLY_SERVER a
WHERE a.APPLY_NAME = '&APPLY_NAME'
AND s.SID = a.SID
AND p.HASH_VALUE = s.SQL_HASH_VALUE
/


18 - Stop Propagation 

set heading off
set linesize 1000
set feedback off;
spool /tmp/run_stop_props.sql
select 'exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('||''''||propagation_name||''');' from dba_propagation
/
spool off
set heading on;
set linesize 80
set feedback on

@/tmp/run_stop_props.sql


19 - Stop Capture 


set heading off
set feedback off
set linesize 255

spool /tmp/stop_capture.sql

select 'exec dbms_capture_adm.stop_capture(''' || CAPTURE_NAME || ''');'
from dba_capture
/
spool off
set feedback on;
@/tmp/stop_capture.sql
!rm /tmp/stop_capture.sql


20 - Stop Apply 

set heading off
set feedback off
set linesize 255

spool /tmp/stop_apply.sql

select 'exec dbms_apply_adm.stop_apply(''' || APPLY_NAME || ''');'
from dba_apply
where status = 'ENABLED'
/
spool off
set feedback on;
@/tmp/stop_apply.sql
!rm /tmp/stop_apply.sql



21- Start Capture 


set feedback off
set heading off
set linesize 255

spool /tmp/start_capture.sql

select 'exec dbms_capture_adm.start_capture(''' || CAPTURE_NAME || ''');'
from dba_capture
where status != 'ENABLED'
/
spool off
set feedback on;
set heading on;
set linesize 80
@/tmp/start_capture.sql
!rm /tmp/start_capture.sql



22- Start Propagation 

set heading off
set linesize 1000
set feedback off;
spool /tmp/run_enable_props.sql
select 'exec DBMS_PROPAGATION_ADM.START_PROPAGATION('||''''||propagation_name||''');' from dba_propagation where status!='ENABLED'
/
spool off
set heading on;
set feedback on
set linesize 80

@/tmp/run_enable_props.sql


23- Start Apply 


set heading off
set feedback off
set linesize 255

spool /tmp/start_apply.sql

select 'exec dbms_apply_adm.start_apply(''' || APPLY_NAME || ''');'
from dba_apply
where status != 'ENABLED'
/
spool off
set feedback on;
@/tmp/start_apply.sql
!rm /tmp/start_apply.sql



24- Set Capture Trace On 

PROMPT
PROMPT Setting capture trace_level = 127...
execute dbms_capture_adm.set_parameter('&&CAPTURE_NAME','trace_level','127');
undefine CAPTURE_NAME


25- Set Capture Trace Off 

PROMPT
PROMPT Setting capture trace_level = NULL...
execute dbms_capture_adm.set_parameter('&&CAPTURE_NAME','trace_level',null);
PROMPT 
undefine CAPTURE_NAME


26- Check Archival Need 


set verify off;
column name format a60

select name, to_char(first_time, 'DD-MON-YY HH24:MI') first_time
from dba_registered_archived_log where &SCN between first_scn and next_scn;
set verify on;



27- Set Apply Trace On 

PROMPT
PROMPT Setting apply trace_level = 127...
execute dbms_apply_adm.set_parameter('&&APPLY_NAME','trace_level','127');
undefine APPLY_NAME


28 - Set Apply Trace Off 

PROMPT
PROMPT Setting apply trace_level = NULL...
execute dbms_apply_adm.set_parameter('&&APPLY_NAME','trace_level',null);
PROMPT 
undefine APPLY_NAME


29- Check Apply Errors In detail 


column SOURCE_DATABASE format a21
column ERROR_MESSAGE format a40
column SOURCE_TRANSACTION_ID format a10
column source_scn format 9999999999999999
column LOCAL_TRANSACTION_ID format a10

set linesize 120
set feedback off

select 
    source_database
   ,source_transaction_id
   ,source_commit_scn SOURCE_SCN
   ,local_transaction_id
   ,error_message
from dba_apply_error
order by ERROR_CREATION_TIME
;

set linesize 80
set feedback on



30 - Check Apply Servers 

set lines 140 pages 80 verify off echo off feedb off
col apply_name for a25
col i for 999
col a# for 999
col s# for 999
col state for a24 wrap
col t for a16
col source_tim for a20 head "Source Time (local)"
col apply_tim for a20 head "Dest Time (local)"
break on apply_name skip 1
ttitle right "Last Applied Transaction                      "
select APPLY_NAME,SERVER_ID s#, sid,
STATE,TOTAL_ASSIGNED trans,TOTAL_MESSAGES_APPLIED msgs,
to_char(APPLIED_MESSAGE_CREATE_TIME,'DD-MON-YY HH24:MI:SS') source_tim,to_char(APPLY_TIME,'DD-MON-YY HH24:MI:SS') apply_tim
from gv$streams_apply_server
order by APPLY_NAME,SERVER_ID
/
ttitle off


31- Check Latency 

set heading off
col l format a60

SELECT 'Latency from '
       ||SOURCE
       ||' to '
       ||d.NAME
       ||' (secs) = ' l,
       ( CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - CAST(SEND AS DATE) ) * 24 * 60 * 60 d
FROM   strmmon.REP_HEARTBEAT_HIST,
       V$DATABASE d
WHERE  SEND IN (SELECT MAX(SEND)
                FROM   strmmon.REP_HEARTBEAT_HIST
                GROUP  BY SOURCE);

set heading on;


32- CHECK ALL 



set feedback off;
set timing off;

--PROMPT **************************************************
--PROMPT **         STREAMS STATUS CHECK                 **
--PROMPT **************************************************
set heading off
SELECT '**    STREAMS STATUS CHECK at ' || to_char(SYSDATE,'DD-MON HH24:MI')   || ' LOCAL TIME    **'
from dual;

set heading off
col l format a60

SELECT 'Latency from '
       ||SOURCE
       ||' to '
       ||d.NAME
       ||' (secs) = ' l,
       ( CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - CAST(SEND AS DATE) ) * 24 * 60 * 60 d
FROM   strmmon.REP_HEARTBEAT_HIST,
       V$DATABASE d
WHERE  SEND IN (SELECT MAX(SEND)
                FROM   strmmon.REP_HEARTBEAT_HIST
                GROUP  BY SOURCE);

set heading on;

-- Capture Status

column capture_name format a25
column status format a28
column status_change_time format a15
column capture_time format a15
column time format a5
column capture_msg format 99999999999999999
column start_scn format 99999999999999999999
column first_scn format 99999999999999999999
column captured_scn format 9999999999999999999

select c.capture_name
     , c.status || ' - ' || DECODE(v.state,'DICTIONARY INITIALIZATION','DICT. INIT','CAPTURING CHANGES','CAPT CHANGES','ENQUEUING MESSAGE','ENQUEUING MSG','WAITING FOR REDO','WAIT FOR REDO','PAUSED FOR FLOW CONTROL','PAUSE FLOW CTRL','DICTIONARY INITIALIZATION','DICTIONARY INIT','WAITING FOR APPLY TO START','WAIT APPLY START','CONNECTING TO APPLY DATABASE','CONN APPLY DB','WAITING FOR PROPAGATION TO START','WAIT PROP STRT',v.state) || ' n' || inst_id STATUS 
 --  , to_char(c.status_change_time,'DD-MON-YY HH24:MI') STATUS_CHANGE_TIME
     , v.capture_message_number CAPTURE_MSG
     , to_char(v.capture_time,'HH24:MI') TIME
from dba_capture c, gv$streams_capture v
where c.capture_name = v.capture_name (+)
/

-- Propagation Status --

column propagation_status format a79

select REPLACE(REPLACE(s.DESTINATION,'"',''),'STRMADMIN.','') || ' State:' || DECODE(s.SCHEDULE_DISABLED,'Y','DISABLED','N','ENABLED ', 'UNKNOWN ')  || ' Msgs:' || p.total_msgs || ' Errs:' || FAILURES PROPAGATION_STATUS
FROM dba_queue_schedules s, gv$propagation_sender p
where p.dblink = s.destination
and s.message_delivery_mode='PERSISTENT';


-- Queue Status --

column QUEUE_NAME format a30

select QUEUE_NAME || ' n' || inst_id QUEUE_NAME,NUM_MSGS,SPILL_MSGS 
from gv$buffered_queues
where queue_schema != 'SYS';

COLUMN APPLY_NAME FORMAT A25
COLUMN STATE HEADING 'Apply Srv State' FORMAT A16
COLUMN msg_number FORMAT 99999999999999999
COLUMN dq_time format a6
COLUMN Status format a12

set linesize 80

-- Apply Status --

SELECT r.APPLY_NAME
  ,ap.status || ' n' || srv.inst_id  STATUS
  , DECODE(srv.state, 'EXECUTE TRANSACTION','EXECUTE TXN', srv.state) STATE
  ,r.DEQUEUED_MESSAGE_NUMBER MSG_NUMBER
  ,to_char(r.DEQUEUE_TIME,'HH24:MI') TIME
FROM gV$STREAMS_APPLY_READER r, DBA_APPLY ap, gV$STREAMS_APPLY_SERVER srv
WHERE r.inst_id=srv.inst_id
AND r.APPLY_NAME = ap.APPLY_NAME
AND r.APPLY_NAME = srv.APPLY_NAME
UNION
SELECT apply_name
   ,status
   , '(disabled)' state
   , NULL msg_number
   , NULL TIME 
FROM DBA_APPLY
WHERE status != 'ENABLED'
/

-- Apply Error Count and Disable_on_Error Status
column error_queue format a25
column disable_on_error format a27

 select apply_name
   , decode(value,'Y','Disable_On_Error: TRUE','N','Disable_On_Error: FALSE !!') DISABLE_ON_ERROR
   , 'Errors_In_Err_Queue: ' || NVL(err_tab.ct,0)  ERROR_QUEUE
 from dba_apply_parameters, (
    select apply_name an, count(*) ct
    from dba_apply_error
    group by apply_name) err_tab
where  dba_apply_parameters.apply_name = err_tab.an (+)
and parameter = 'DISABLE_ON_ERROR';


33- Heartbeat 


set pages 300 lines 300
col SECS_SINCE_LAST_HEARTBEAT for a30
col SEND for a30
col RECV for a30
col SOURCE for a20
col LATENCY for a30
select  send, recv, source,
        sys_extract_utc(systimestamp) - recv secs_since_last_heartbeat,
        recv - send latency
  from (select send, recv, source, row_number() over (partition by source order by recv desc ) rn
  from rep_heartbeat_hist h
 where recv is not null
   and rcvdb_name = (select db_unique_name from v$database) || '.WORLD'
and recv > sys_extract_utc(systimestamp) - 0.1)
  where rn = 1;




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)