REM REM This healthcheck script is for use on Oracle12cR1 databases only. (12.1.0.1) REM REM REM Do not use this script on Oracle9iR2 Oracle 10g, or Oracle 11g Streams configurations. REM REM It is recommended to run with markup html ON (default is on) and generate an HTML file for web viewing. REM Please provide the output in HTML format when Oracle (support or development) requests healthcheck output. REM To convert output to a text file viewable with a text editor, REM change the HTML ON to HTML OFF in the set markup command REM Remember to set up a spool file to capture the output REM --connect / as sysdba define hcversion = 'v7.0.3'; set truncate off set numwidth 15 set lines 240 set markup HTML ON entmap off alter session set nls_date_format='YYYY-MM-DD HH24:Mi:SS'; alter session set nls_language=american; set heading off select 'STREAMS Health Check (&hcversion) for '||global_name||' on Instance='||instance_name||' generated: '||sysdate o from global_name, v$instance; set heading on timing off prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ==================================================== prompt =====================Summary ============================== prompt ==================================================== prompt prompt ++ Summary Overview ++ prompt COL NAME HEADING 'Name' col platform_name format a30 wrap col current_scn format 99999999999999999 col host Heading 'Host' col version heading 'Version' col startup_time heading 'Startup|Time' col database_role Heading 'Database|Role' SELECT db.DBid,db.name, db.platform_name ,i.HOST_NAME HOST, i.VERSION, i.instance_number instance,db.cdb,db.database_role,db.current_scn, db.min_required_capture_change# from v$database db,v$instance i; prompt prompt Summary of Streams Capture configured in database (ConfigDetails StatsDetails) prompt col capture_name format a20 heading 'Capture|Name' col capture_type format a10 heading 'Capture|Type' col real_time_mine format a8 heading 'RealTime|Mine?' col source_database format a20 heading 'Source DB|Name' col purpose format a25 heading 'Purpose' col status Heading 'Status' col state Heading 'Current|Capture|State' col capture_user format a12 Heading 'Capture|User' col required_checkpoint_scn format 999999999999999999 heading 'Required|Checkpoint|SCN' col inst_id Heading 'Instance' col version format a12 Heading 'Capture|Version' col startup_time heading 'Process|Startup|Time' col mined_MB Heading 'Redo|Mined|MB' format 99999999.999 col sent_MB Heading 'Sent to|Extract|Mb' format 99999999.999 col STATE_CHANGED_TIME Heading 'Last |State Changed|Time' col Current_time Heading 'Current|Time' col capture_lag Heading 'Capture|Lag|seconds' select SYSDATE Current_time, c.capture_name, c.capture_user, c.capture_type, decode(cp.value,'N','NO', 'YES') Real_time_mine, c.required_checkpoint_scn, c.purpose, c.version, c.logminer_id, c.status, DECODE (g.STATE,null,' WAITING FOR CLIENT REQUESTS ', 'WAITING FOR INACTIVE DEQUEUERS',''||g.state||'', g.state) State, (SYSDATE- g.capture_message_create_time)*86400 capture_lag, g.bytes_of_redo_mined/1024/1024 mined_MB, g.startup_time, g.inst_id, c.source_database from dba_capture c, gv$streams_capture g, dba_capture_parameters cp where c.capture_name=g.capture_name and c.purpose != 'GoldenGate Capture' and c.capture_name=cp.capture_name and cp.parameter='DOWNSTREAM_REAL_TIME_MINE' and c.status='ENABLED' union all select SYSDATE Current_time, c.capture_name, c.capture_user, c.capture_type, decode(cp.value, 'N','NO', 'YES') Real_time_mine, c.required_checkpoint_scn, c.purpose, c.version, c.logminer_id, c.status, 'Unavailable', NULL, NULL, NULL, NULL, c.source_database from dba_capture c, dba_capture_parameters cp where c.status in ('DISABLED','ABORTED') and c.purpose != 'GoldenGate Capture' and c.capture_name=cp.capture_name and cp.parameter='DOWNSTREAM_REAL_TIME_MINE' order by capture_name; prompt prompt prompt prompt Capture key parameters (Details) prompt col parallelism format a11 col sga_size format a12 col max_sga_size format a12 col excludetag format a20 col excludeuser format a20 col getapplops format a10 col getreplicates format a13 col checkpoint_frequency format a20 select cp.capture_name, max(case when parameter='PARALLELISM' then value end) parallelism ,max(case when parameter='_SGA_SIZE' then value end) sga_size ,max(case when parameter='MAX_SGA_SIZE' then value end) max_sga_size ,max(case when parameter='_CHECKPOINT_FREQUENCY' then value end) checkpoint_frequency from dba_capture_parameters cp, dba_capture c where c.capture_name=cp.capture_name and c.purpose !='GoldenGate Capture' group by cp.capture_name; prompt prompt Capture Logminer session info (Details) prompt col session_name Heading 'Capture|Name' col available_txn Heading 'Available|Chunks' col delivered_txn Heading 'Delivered|Chunks' col difference Heading 'Ready to Send|Chunks' col builder_work_size Heading 'Builder|WorkSize' col prepared_work_size Heading 'Prepared|WorkSize' col used_memory_size Heading 'Used|Memory' col max_memory_size Heading 'Max|Memory' col used_mem_pct Heading 'Used|Memory|Percent' select session_name, available_txn, delivered_txn, available_txn-delivered_txn as difference, builder_work_size, prepared_work_size, used_memory_size , max_memory_size, (used_memory_size/max_memory_size)*100 as used_mem_pct FROM v$logmnr_session order by session_name; prompt prompt prompt Summary of Apply processes configured in this database(ConfigDetails StatsDetails) prompt col server_name format a20 heading 'Server|Name' col status Heading 'Status' col state format a30 Heading 'Current|Apply|State' col active_server_count Heading 'Active|Server|Count' col inst_id Heading 'Instance' col unassigned_complete_txns Heading 'Unassigned|Complete|Txns' col apply_user format a12 Heading 'Apply|User' col startup_time heading 'Process|Startup|Time' col lwm heading 'Low Watermark|Message|Create Time' col apply_tag heading 'Apply|Tag' format a20 select sysdate Current_time, ib.apply_name, ib.apply_user, ib.status, ib.purpose, ib.apply_tag, g.state, g.unassigned_complete_txns, g.lwm_message_create_time lwm, g.startup_time, g.inst_id, pg.source_database from dba_apply ib, gv$streams_apply_coordinator g, dba_apply_progress pg where ib.apply_name=g.apply_name and ib.apply_name=pg.apply_name and ib.purpose not like 'GoldenGate%' and ib.status in ('ATTACHED','ENABLED') union all select sysdate Current_time, ib.apply_name, ib.apply_user, ib.status, ib.purpose, ib.apply_tag, 'Unavailable', null, null, null, null, pg.source_database from dba_apply ib, dba_apply_progress pg where ib.apply_name=pg.apply_name and ib.status not in ('ATTACHED','ENABLED') and ib.purpose not like 'GoldenGate%' order by apply_name; prompt prompt Apply key parameters (Details) prompt col max_parallelism format a15 col parallelism format a11 col hash_table_size format a15 col txn_buffer_size format a15 col max_sga_size format a12 col commit_serialization format a25 col optimize_progress_table format a23 col trace_level format a11 select ap.apply_name, max(case when parameter='PARALLELISM' then value end) parallelism ,max(case when parameter='COMMIT_SERIALIZATION' then value end) commit_serialization ,max(case when parameter='_HASH_TABLE_SIZE' then value end) hash_table_size ,max(case when parameter='_TXN_BUFFER_SIZE' then value end) txn_buffer_size ,max(case when parameter='TRACE_LEVEL' then value end) trace_level ,max(case when parameter='MAX_PARALLELISM' then value end) max_parallelism ,max(case when parameter='OPTIMIZE_PROGRESS_TABLE' then value end) optimize_progress_table ,max(case when parameter='MAX_SGA_SIZE' then value end) max_sga_size from dba_apply_parameters ap, dba_apply ib where ib.apply_name=ap.apply_name and ib.purpose not like 'GoldenGate%' group by ap.apply_name; prompt prompt +++ Outstanding alerts (Details) prompt set feedback on select message_type,creation_time,reason, suggested_action, module_id,object_type, instance_name||' (' ||instance_number||' )' Instance, time_suggested from dba_outstanding_alerts where creation_time >= sysdate -10 and rownum < 11 order by creation_time desc; prompt prompt Count of Capture and Apply processes configured in database by purpose col nmbr heading 'Count' col type heading 'Process|Type' select purpose,count(*) nmbr, 'CAPTURE' type from dba_capture group by purpose union all select purpose, count(*) nmbr, 'APPLY' type from dba_apply group by purpose order by purpose; prompt -- note: this function is vulnerable to SQL injection, please do not copy it create or replace function get_parameter( param_name IN varchar2, param_value IN OUT varchar2, table_name IN varchar2, table_param_name IN varchar2, table_value IN varchar2 ) return boolean is statement varchar2(4000); begin -- construct query statement := 'select ' || table_value || ' from ' || table_name || ' where ' || table_param_name || '=''' || param_name || ''''; begin execute immediate statement into param_value; exception when no_data_found then -- data is not found, so return FALSE return FALSE; end; -- data found, so return TRUE return TRUE; end get_parameter; / show errors; create or replace procedure verify_init_parameter( param_name IN varchar2, expected_value IN varchar2, verbose IN boolean, more_info IN varchar2 := NULL, more_info2 IN varchar2 := NULL, at_least IN boolean := FALSE, is_error IN boolean := FALSE, use_like IN boolean := FALSE, -- may not be necessary alert_if_not_found IN boolean := TRUE ) is current_val_num NUMBER; expected_val_num NUMBER; current_value varchar2(512); prefix varchar2(20); matches boolean := FALSE; comparison_str varchar2(20); begin -- Set prefix as warning or error if is_error then prefix := '+ ERROR: '; else prefix := '+ WARNING: '; end if; -- Set comparison string if at_least then comparison_str := ' at least '; elsif use_like then comparison_str := ' like '; else comparison_str := ' set to '; end if; -- Get value if get_parameter(param_name, current_value, 'v$parameter', 'name', 'value') = FALSE and alert_if_not_found then -- Value isn't set, so output alert dbms_output.put_line(prefix || 'The parameter ''' || param_name || ''' should be' || comparison_str || '''' || expected_value || ''', instead it has been left to its default value.'); if verbose and more_info is not null then dbms_output.put_line(more_info); if more_info2 is not null then dbms_output.put_line(more_info2); end if; end if; dbms_output.put_line('+'); return; end if; -- See if the expected value is what is actually set if use_like then -- Compare with 'like' if current_value like '%'||expected_value||'%' then matches := TRUE; end if; elsif at_least then -- Do at least current_val_num := to_number(current_value); expected_val_num := to_number(expected_value); if current_val_num >= expected_val_num then matches := TRUE; end if; else -- Do normal comparison if current_value = expected_value then matches := TRUE; end if; end if; if matches = FALSE then -- The values don't match, so alert dbms_output.put_line(prefix || 'The parameter ''' || param_name || ''' should be' || comparison_str || '''' || expected_value || ''', instead it has the value ''' || current_value || '''.'); if verbose and more_info is not null then dbms_output.put_line(more_info); if more_info2 is not null then dbms_output.put_line(more_info2); end if; end if; dbms_output.put_line('+'); end if; end verify_init_parameter; / show errors; prompt prompt prompt ++ prompt ++ Notifications ++ prompt ++ prompt set serveroutput on size 50000 declare -- Change the variable below to FALSE if you just want the warnings and errors, not the advice verbose boolean := TRUE; -- By default any errors in dba_apply_error will result in output apply_error_threshold number := 0; -- By default a streams pool usage above 95% will result in output streams_pool_usage_threshold number := 95; -- The total number of registered archive logs to have before reporting an error registered_logs_threshold number := 1000; -- The total number of days old the oldest archived log should be before reporting an error registered_age_threshold number := 60; -- days row_count number; days_old number; failed boolean; streams_pool_usage number; streams_pool_size varchar2(512); cursor apply_error is select distinct apply_name from dba_apply_error; cursor aborted_apply is select apply_name, error_number, error_message from dba_apply where status='ABORTED'; cursor aborted_capture is select capture_name, error_number, error_message from dba_capture where status='ABORTED'; cursor aborted_prop is select propagation_name, last_error_date, last_error_msg from dba_propagation, dba_queue_schedules where schema = source_queue_owner and qname = source_queue_name and destination = destination_dblink and schedule_disabled = 'Y' and message_delivery_mode = 'BUFFERED'; cursor disabled_apply is select apply_name from dba_apply where status='DISABLED'; cursor disabled_capture is select capture_name from dba_capture where status='DISABLED'; cursor unattached_capture is select capture_name from gv$streams_capture where state='WAITING FOR INACTIVE DEQUEUERS'; cursor classic_capture is select capture_name from dba_capture where capture_name like 'OGG%$%' and purpose='Streams'; -- check if state_changed_time is older than 3 minutes (approx .00211 * 86400) cursor old_state_time is select capture_name,state,state_changed_time,to_char( (SYSDATE- state_changed_time)*1440,'99990.99') mins from gv$streams_capture where (SYSDATE - state_changed_time ) >.00211; cursor ckpt_retention_time is select capture_name, DECODE(checkpoint_retention_time,60,'WARNING: Checkpoint Retention time is set too high (60 days) for capture ', 'INFO: Checkpoint Retention time set to '||checkpoint_retention_time||' days ') msg from dba_capture where purpose != 'GoldenGate Capture'; begin -- Check for aborted capture processes for rec in aborted_capture loop dbms_output.put_line('+ ERROR: Capture ''' || rec.capture_name || ''' has aborted with message ' || rec.error_message); end loop; dbms_output.put_line('+'); -- Check for aborted apply processes for rec in aborted_apply loop dbms_output.put_line('+ ERROR: Apply ''' || rec.apply_name || ''' has aborted with message ' || rec.error_message); if verbose then -- Try to give some suggestions -- TODO: include other errors, suggest how to recover if rec.error_number = 26714 then dbms_output.put_line('+ This apply aborted because a non-fatal user error has occurred and the ''disable_on_error'' parameter is ''Y''.'); dbms_output.put_line('+ Please resolve the errors and restart the apply. Setting the ''disable_on_error'' parameter to ''N'' will prevent'); dbms_output.put_line('+ apply from aborting on user errors in the future. Note the errors should still be resolved though.'); dbms_output.put_line('+'); elsif rec.error_number = 26688 then dbms_output.put_line('+ This apply aborted because a column value in a particular change record belonging to a key was not found. '); dbms_output.put_line('+ For more information, search the trace files for ''26688'' and view the relevant trace file.'); dbms_output.put_line('+'); end if; end if; end loop; dbms_output.put_line('+'); -- Check for apply errors in the error queue for rec in apply_error loop select count(*) into row_count from dba_apply_error where rec.apply_name = apply_name; if row_count > apply_error_threshold then dbms_output.put_line('+ ERROR: Apply ''' || rec.apply_name || ''' has placed ' || row_count || ' transactions in the error queue! Please check the dba_apply_error view.'); end if; end loop; dbms_output.put_line('+'); -- Check for aborted propagation for rec in aborted_prop loop dbms_output.put_line('+ ERROR: Propagation ''' || rec.propagation_name || ''' has aborted with most recent error message:'); dbms_output.put_line('+ ''' || rec.last_error_msg || ''''); dbms_output.put_line('+'); end loop; -- Check for disabled capture processes for rec in disabled_capture loop dbms_output.put_line('+ WARNING: Capture ''' || rec.capture_name || ''' is disabled'); end loop; dbms_output.put_line('+'); -- Check for disabled apply processes for rec in disabled_apply loop dbms_output.put_line('+ WARNING: Apply ''' || rec.apply_name || ''' is disabled'); end loop; dbms_output.put_line('+'); -- Check for classic capture processes for rec in classic_capture loop dbms_output.put_line('+ WARNING: Capture ''' || rec.capture_name || ''' is Oracle GoldenGate classic capture with LOGRETENTION enabled'); end loop; dbms_output.put_line('+'); --- capture is started but extract is not attached for rec in unattached_capture loop dbms_output.put_line('+ ERROR: Capture '''||rec.capture_name||''' is not attached to client. State is WAITING FOR INACTIVE DEQUEUERS'); dbms_output.put_line('+ Start the XStream Outbound Server client application'); dbms_output.put_line('+'); end loop; dbms_output.put_line('+'); --- capture state has not changed for at least 3 minutes for rec in old_state_time loop dbms_output.put_line('+ WARNING: Capture State for '||rec.capture_name||' has not changed for over '|| rec.mins||' minutes.'); dbms_output.put_line('+ Last Capture state change timestamp is '||rec.state_changed_time||' State is '||rec.state); dbms_output.put_line('+ '); end loop; -- Check for too many registered archive logs begin failed := FALSE; select count(*) into row_count from dba_registered_archived_log where purgeable = 'NO'; select (sysdate - min(modified_time)) into days_old from dba_registered_archived_log where purgeable = 'NO'; if row_count > registered_logs_threshold then failed := TRUE; dbms_output.put_line('+ WARNING: ' || row_count || ' archived logs registered.'); end if; if days_old > registered_age_threshold then failed := TRUE; dbms_output.put_line('+ WARNING: The oldest archived log is ' || round(days_old) || ' days old!'); end if; select count(*) into row_count from dba_registered_archived_log where purgeable = 'YES'; if row_count > registered_logs_threshold/2 then dbms_output.put_line('+ WARNING: There are '|| row_count ||' archived logs ready to be purged from disk.'); dbms_output.put_line('+ Use the following select to identify unneeded logfiles:'); dbms_output.put_line('+ select name from dba_registered_archived_log where purgeable = "YES" '); end if; if failed then dbms_output.put_line('+ A restarting Capture process must mine through each registered archive log.'); dbms_output.put_line('+ To speedup Capture restart, reduce the amount of disk space taken by the archived'); dbms_output.put_line('+ logs, and reduce Capture metadata, consider moving the first_scn automatically by '); dbms_output.put_line('+ altering the checkpoint_retention_time capture parameter to a lower value '); dbms_output.put_line('+ (See the Documentation for more information). '); dbms_output.put_line('+ Note that once the first scn is increased, Capture will no longer be able to mine before'); dbms_output.put_line('+ this new scn value.'); dbms_output.put_line('+ Successive moves of the first_scn will remove unneeded registered archive'); dbms_output.put_line('+ logs only if the files have been removed from disk'); end if; end; end; / prompt prompt ++ prompt ++ SYS Checks prompt ++ prompt declare -- Change the variable below to FALSE if you just want the warnings and errors, not the advice verbose boolean := TRUE; -- By default a streams pool usage above 95% will result in output streams_pool_usage_threshold number := 95; row_count number; days_old number; failed boolean; streams_pool_usage number; streams_pool_size varchar2(512); cursor unrecovered_queue is select queue_schema,queue_name from x$buffered_queues where flags=1; begin -- Check high streams pool usage begin select FRUSED_KWQBPMT into streams_pool_usage from x$kwqbpmt; select value into streams_pool_size from v$parameter where name = 'streams_pool_size'; if streams_pool_usage > streams_pool_usage_threshold then dbms_output.put_line('+ WARNING: Streams pool usage for this instance is ' || streams_pool_usage || '% of ' || streams_pool_size || ' bytes!'); dbms_output.put_line('+ If this system is processing a typical workload, and no ' || 'other errors exist, consider increasing the streams pool size.'); end if; exception when others then null; end; dbms_output.put_line('+'); -- Check unrecovered queues for rec in unrecovered_queue loop dbms_output.put_line('+ ERROR: Queue ''' || rec.queue_schema || '.'||rec.queue_name||' has not been recovered normally ' ); dbms_output.put_line('+ Force recovery by altering the queue ownership to another instance. '); dbms_output.put_line('+ Use the DBMS_AQADM.ALTER_QUEUE_TABLE procedure to specify a different instance.'); end loop; dbms_output.put_line('+'); end; / prompt prompt ++ prompt ++ init.ora checks ++ prompt ++ declare -- Change the variable below to FALSE if you just want the warnings and errors, not the advice verbose boolean := TRUE; row_count number; num_downstream_cap number; capture_procs number; apply_procs number; newline varchar2(1) := ' '; begin -- Error checks first verify_init_parameter('global_names', 'TRUE', verbose, is_error=>FALSE); verify_init_parameter('open_links', '4', verbose, at_least=> TRUE, is_error=>TRUE, alert_if_not_found=>FALSE); -- Get minimum number of parallel_max_servers to set -- Do downstream capture checks select count(*) into num_downstream_cap from dba_capture where capture_type = 'DOWNSTREAM'; if num_downstream_cap > 0 then -- We have a downstream capture, so do specific checks verify_init_parameter('remote_archive_enable', 'TRUE', verbose, is_error=>TRUE); end if; -- Then warnings verify_init_parameter('compatible', '12.', verbose, '+ To use the new features introduced in Oracle Database 12g Release 1, '|| 'this parameter must be set to a value greater than ''12.''', use_like => TRUE); -- explictly check if aq_tm_processes has been manually set to 0. If so, raise error. declare mycheck number; begin select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and value = '0' and (ismodified <> 'FALSE' OR isdefault='FALSE'); if mycheck = 1 then dbms_output.put_line('+ ERROR: The parameter ''aq_tm_processes'' should not be explicitly set to 0!'); dbms_output.put_line('+ Queue monitoring is disabled for all queues.'); dbms_output.put_line('+ To resolve this problem, set the value to 1 using: ALTER SYSTEM SET AQ_TM_PROCESSES=1; '); end if; exception when no_data_found then null; end; -- explictly check if aq_tm_processes has been manually set to 10. If so, raise error. declare mycheck number; begin select 1 into mycheck from v$parameter where name = 'aq_tm_processes' and isdefault = 'FALSE' and value = '10'; if mycheck = 1 then dbms_output.put_line('+ ERROR: The parameter ''aq_tm_processes'' should not be explicitly set to 10!'); dbms_output.put_line('+ With this setting, queue monitoring is disabled for buffered queues.'); dbms_output.put_line('+ To resolve this problem, set the value to 1 using: ALTER SYSTEM SET AQ_TM_PROCESSES=1; '); end if; exception when no_data_found then null; end; verify_init_parameter('streams_pool_size', '0', TRUE, '+ If this parameter is 0 and sga_target is non-zero, then autotuning of the streams pool is implied.'||newline|| '+ If the sga_target parameter is set to 0 and streams_pool_size is 0,'|| newline|| '+ 10% of the shared pool will be used for Streams.' || newline || '+ If sga_target is 0, the minimum recommendation for streams_pool_size is 200M.'|| newline|| '+ Note you must bounce the database if changing the ', '+ value from zero to a nonzero value. But if simply increasing this' || newline || '+ value from an already nonzero value, the database need not be bounced.', at_least=> TRUE); end; / prompt prompt ++ prompt ++ Configuration checks ++ prompt ++ declare current_value varchar2(4000); cursor propagation_latency is select propagation_name, latency from dba_propagation, dba_queue_schedules where schema = source_queue_owner and qname = source_queue_name and destination = destination_dblink and latency >= 60 and message_delivery_mode = 'BUFFERED'; cursor multiqueues is select c.capture_name capture_name, a.apply_name apply_name, c.queue_owner queue_owner, c.queue_name queue_name from dba_capture c, dba_apply a where c.queue_name = a.queue_name and c.queue_owner = a.queue_owner and c.capture_type != 'DOWNSTREAM' and a.purpose ='STREAMS APPLY' and c.capture_name not like 'CDC$%'; cursor nonlogged_tables is select table_owner owner,table_name name from dba_capture_prepared_tables t where table_owner in (select distinct(table_owner) from dba_capture_prepared_tables where supplemental_log_data_pk='NO' and supplemental_log_data_fk='NO' and supplemental_log_data_ui='NO' and supplemental_log_data_all='NO' minus select schema_name from dba_capture_prepared_schemas) and not exists (select 'X' from dba_log_groups l where t.table_owner = l.owner and t.table_name = l.table_name UNION select 'x' from dba_capture_prepared_database); cursor overlapping_rules is select a.streams_name sname, a.streams_type stype, a.rule_set_owner rule_set_owner, a.rule_set_name rule_set_name, a.rule_owner owner1, a.rule_name name1, a.streams_rule_type type1, b.rule_owner owner2, b.rule_name name2, b.streams_rule_type type2 from dba_streams_rules a, dba_streams_rules b where a.rule_set_owner = b.rule_set_owner and a.rule_set_name = b.rule_set_name and a.streams_name = b.streams_name and a.streams_type = b.streams_type and a.rule_type = b.rule_type and (a.subsetting_operation is null or b.subsetting_operation is null) and (a.rule_owner != b.rule_owner or a.rule_name != b.rule_name) and ((a.streams_rule_type = 'GLOBAL' and b.streams_rule_type in ('SCHEMA', 'TABLE') and a.schema_name = b.schema_name) or (a.streams_rule_type = 'SCHEMA' and b.streams_rule_type = 'TABLE' and a.schema_name = b.schema_name) or (a.streams_rule_type = 'TABLE' and b.streams_rule_type = 'TABLE' and a.schema_name = b.schema_name and a.object_name = b.object_name and a.rule_name < b.rule_name) or (a.streams_rule_type = 'SCHEMA' and b.streams_rule_type = 'SCHEMA' and a.schema_name = b.schema_name and a.rule_name < b.rule_name) or (a.streams_rule_type = 'GLOBAL' and b.streams_rule_type = 'GLOBAL' and a.rule_name < b.rule_name)) order by a.rule_name; cursor spilled_apply is select a.apply_name from dba_apply_parameters p, dba_apply a, gv$buffered_queues q where a.queue_owner = q.queue_schema and a.queue_name = q.queue_name and a.apply_name = p.apply_name and p.parameter = 'PARALLELISM' and p.value > 1 and (q.cspill_msgs/DECODE(q.cnum_msgs, 0, 1, q.cnum_msgs) * 100) > 25; cursor bad_source_db is select rule_owner||'.'||rule_name Rule_name, source_database from dba_streams_rules where source_database not in (select global_name from system.logmnrc_dbname_uid_map); cursor qtab_too_long is select queue_table name, length(queue_table) len from dba_queues q , dba_apply a where length(queue_table)>24 and q.owner=a.queue_owner and q.name=a.queue_name; cursor reginfo_invalid is select comp_id,status from dba_registry where comp_id in ('CATALOG','CATPROC') and status <> 'VALID'; cursor version_diff is select i.version inst_version,r.version reg_version from v$instance i, dba_registry r where r.comp_id in ('CATALOG','CATPROC') and i.version <> r.version; row_count number; capture_count number; local_capture_count number := 0; verbose boolean := TRUE; overlap_rules boolean := FALSE; latency number; begin -- Check that propagation latency is not 60 for rec in propagation_latency loop dbms_output.put_line('+ WARNING: the Propagation process ''' || rec.propagation_name || ''' has latency ' || rec.latency || ', it should be 5 or less!'); if verbose then dbms_output.put_line('+ Set the latency by calling ' || 'dbms_aqadm.alter_schedule(queue_name,destination,latency=>5)'); end if; dbms_output.put_line('+'); end loop; -- Check source database names on rules for rec in bad_source_db loop dbms_output.put_line('+ ERROR: The rule '||rec.rule_name||' specifies an unknown source database: '||rec.source_database||'.'); dbms_output.put_line('+ Make sure that the associated capture process has completed processing through the initial dictionary load.'); end loop; -- Check queue table name length for rec in qtab_too_long loop dbms_output.put_line('+ ERROR: The queue table name '''||rec.name||''' has length of '||rec.len|| ' bytes, it should be 24 bytes or less!'); dbms_output.put_line('+'); end loop; -- Check Registry Info STATUS for rec in reginfo_invalid loop dbms_output.put_line('+ ERROR: The DBA_REGISTRY status information for component '''||rec.comp_id|| ''' requires attention. Status is '||rec.status|| '. Please recompile the component '); dbms_output.put_line('+'); end loop; -- Check consistent Instance and Registry Info for rec in version_diff loop dbms_output.put_line('+ ERROR: The ORACLE_HOME software is '''||rec.inst_version||''' but the database catalog is '||rec.reg_version|| '. CATPATCH must be run successfully to complete the upgrade'); dbms_output.put_line('+'); end loop; -- Separate queues for capture and apply for rec in multiqueues loop dbms_output.put_line('+ WARNING: the Capture process ''' || rec.capture_name || ''' and Apply process ''' || rec.apply_name || ''''); dbms_output.put_line('+ share the same queue ''' || rec.queue_owner || '.' || rec.queue_name || '''. If the Apply process is receiving changes'); dbms_output.put_line('+ from a remote site, a separate queue should be created for' || ' the Apply process.'); end loop; dbms_output.put_line('+'); -- Make sure it is in archivelog mode select count(*) into capture_count from dba_capture where capture_type != 'DOWNSTREAM'; select count(*) into row_count from v$database where log_mode = 'NOARCHIVELOG'; if row_count > 0 and capture_count > 0 then dbms_output.put_line('+ ERROR: ARCHIVELOG mode must be enabled for this database.'); if verbose then dbms_output.put_line('+ For a Streams Capture process to function correctly, it' || ' must be able to read the archive logs.'); dbms_output.put_line('+ Please refer to the documentation to restart the database' || ' in ARCHIVELOG format.'); dbms_output.put_line('+'); end if; end if; -- Basic supplemental logging checks -- #1. If minimal supplemental logging is not enabled, this is an error select count(*) into row_count from v$database where SUPPLEMENTAL_LOG_DATA_MIN = 'NO'; select count(*) into local_capture_count from dba_capture where capture_type = 'LOCAL'; if row_count > 0 and local_capture_count > 0 then dbms_output.put_line('+ ERROR: Minimal supplemental logging not enabled.'); if verbose then dbms_output.put_line('+ For a Streams Capture process to function correctly, at' || ' least minimal supplemental logging should be enabled.'); dbms_output.put_line('+ Execute ''ALTER DATABASE ADD SUPPLEMENTAL LOG DATA''' || ' to fix this issue. Note you may need to specify further'); dbms_output.put_line('+ levels of supplemental logging, see the documentation' || ' for more details.'); dbms_output.put_line('+'); end if; end if; -- #2. If Primary key database level logging not enabled, there better be some -- log data per prepared table select count(*) into row_count from v$database where SUPPLEMENTAL_LOG_DATA_PK = 'NO'; if row_count > 0 and local_capture_count > 0 then for rec in nonlogged_tables loop dbms_output.put_line('+ ERROR: No supplemental logging specified for table ''' || rec.owner || '.' || rec.name || '''.'); if verbose then dbms_output.put_line('+ In order for Streams to work properly, it must' || ' have key information supplementally logged'); dbms_output.put_line('+ for each table whose changes are being captured. ' || 'This system does not have database level primary key information '); dbms_output.put_line('logged, thus for each interested table manual logging ' || 'must be specified. Please see the documentation for more info.'); dbms_output.put_line('+'); end if; end loop; end if; -- Rules checks -- TODO: intergrate existing rules checks found above for rec in overlapping_rules loop overlap_rules := TRUE; dbms_output.put_line('+ WARNING: The rule ''' || rec.owner1 || '''.''' || rec.name1 || ''' and ''' || rec.owner2 || '''.''' || rec.name2 || ''' from rule set ''' || rec.rule_set_owner || '''.''' || rec.rule_set_name || ''' overlap.'); end loop; if overlap_rules and verbose then dbms_output.put_line('+ Overlapping rules are a problem especially when rule-based transformations exist.'); dbms_output.put_line('+ Streams makes no guarantees of which rule in a rule set will evaluate to TRUE,'); dbms_output.put_line('+ thus overlapping rules will cause inconsistent behavior, and should be avoided.'); end if; dbms_output.put_line('+'); -- -- Suggestions. These might help speedup performance. -- if verbose then -- Propagation has a rule set select count(*) into row_count from dba_propagation where rule_set_owner is not null and rule_set_name is not null; if row_count > 0 then dbms_output.put_line('+ SUGGESTION: One or more propagation processes contain rule sets.'); dbms_output.put_line('+ If a Propagation process will unconditionally forward all incoming'); dbms_output.put_line('+ messages to its destination queue, and no rule-based transformations are'); dbms_output.put_line('+ performed by the Propagation process, you should consider removing'); dbms_output.put_line('+ the rule set for the Propagation process via dbms_propagation_adm.alter_propagation.'); dbms_output.put_line('+ This will improve Propagation performance.'); dbms_output.put_line('+'); end if; -- Apply has a rule set select count(*) into row_count from dba_apply where rule_set_owner is not null and rule_set_name is not null; if row_count > 0 then dbms_output.put_line('+ SUGGESTION: One or more apply processes contain rule sets.'); dbms_output.put_line('+ If an Apply process will unconditionally apply all incoming'); dbms_output.put_line('+ messages and no rule-based transformations or apply enqueues are '); dbms_output.put_line('+ performed by the Apply process, you should consider removing '); dbms_output.put_line('+ the rule set for via dbms_apply_adm.alter_apply.'); dbms_output.put_line('+ This will improve Apply performance.'); dbms_output.put_line('+'); end if; -- Apply has parallelism 1 select count(*) into row_count from dba_apply_parameters where parameter='PARALLELISM' and to_number(value) = 1; if row_count > 0 then dbms_output.put_line('+ SUGGESTION: One or more Apply processes have parallelism 1'); dbms_output.put_line('+ If your workload consists of many independent transactions'); dbms_output.put_line('+ and the apply is the bottleneck of your system, '); dbms_output.put_line('+ Review the following:'); dbms_output.put_line('+ If the PURPOSE column of the apply is "Streams",'); dbms_output.put_line('+ Increase the parallelism of the apply process to a multiple of 4 via dbms_apply_adm.set_parameter.'); dbms_output.put_line('+'); end if; -- If apply parallelism > 1, and spills exist in queue, and _txn_buffer_size -- hasn't been set, suggest reducing it to 10 or less. -- Please note: _txn_buffer_size has no meaning in 11.2.0.2 and above -- Please note: there is no queue spill in CCA mode. for rec in spilled_apply loop begin select value into current_value from dba_apply_parameters where parameter='_TXN_BUFFER_SIZE' and apply_name = rec.apply_name; exception when no_data_found then -- default parameter, output warning dbms_output.put_line('+ SUGGESTION: Apply ''' || rec.apply_name || ''' has parallelism > 1 and queue spilled data.'); dbms_output.put_line('+'); end; end loop; -- Both transformation function and dml handler defined for apply select count(*) into row_count from dba_apply a, dba_streams_rules r, dba_streams_transform_function t, dba_apply_dml_handlers d where a.rule_set_owner = r.rule_set_owner and a.rule_set_name = r.rule_set_name and r.rule_owner = t.rule_owner and r.rule_name = t.rule_name and t.transform_function_name is not null and (a.apply_name = d.apply_name or d.apply_name is null) and (r.schema_name = d.object_owner or r.schema_name is null) and (r.object_name = d.object_name or r.object_name is null) and r.subsetting_operation is null and d.error_handler = 'N' and d.user_procedure is not null; if row_count > 0 then dbms_output.put_line('+ SUGGESTION: One or more Apply processes have both DML handlers and transformation'); dbms_output.put_line('+ functions defined. Both DML handlers and transformations involve expensive'); dbms_output.put_line('+ PL/SQL operations. If you notice slow Apply performance, consider performing'); dbms_output.put_line('+ all PL/SQL operations in either a transformation function or dml handler.'); dbms_output.put_line('+'); end if; -- Database-level supplemental logging defined but only a few tables replicated select count(*) into row_count from v$database where supplemental_log_data_pk = 'YES'; select count(*) into capture_count from dba_capture_prepared_tables; if row_count > 0 and capture_count < 10 and local_capture_count > 0 then dbms_output.put_line('+ SUGGESTION: Database-level supplemental logging enabled but only a few tables'); dbms_output.put_line('+ prepared for capture. Database-level supplemental logging could write more'); dbms_output.put_line('+ information to the redo logs for every update statement in the system.'); dbms_output.put_line('+ If the number of tables you are interested in is small, you might consider'); dbms_output.put_line('+ specifying supplemental logging of keys and columns on a per-table basis.'); dbms_output.put_line('+ See the documentation for more information on per-table supplemental logging.'); dbms_output.put_line('+'); end if; end if; end; / prompt prompt ++ prompt ++ Performance Checks ++ prompt ++ prompt ++ Note: Performance only checked for enabled Streams processes! prompt ++ Aborted and disabled processes will not report performance warnings! prompt declare verbose boolean := TRUE; -- how far back capture must be before we generate a warning capture_latency_threshold number := 300; -- seconds -- how far back the apply reader must be before we generate a warning applyrdr_latency_threshold number := 600; -- seconds -- how far back the apply coordinator's LWM must be before we generate a warning applylwm_latency_threshold number := 1200; -- seconds -- how many messages should be unconsumed before generating a warning unconsumed_msgs_threshold number := 300000; -- percentage of messages spilled before generating a warning spill_ratio_threshold number := 25; -- how long queue can be up before signalling a warning spill_startup_threshold number := 3600; -- seconds -- how long logminer can spend spilling before generating a warning logminer_spill_threshold number := 30000000; -- microseconds complex_rules boolean := FALSE; slow_clients boolean := FALSE; cursor capture_latency (threshold NUMBER) is select capture_name, 86400 *(available_message_create_time - capture_message_create_time) latency from gv$streams_capture where 86400 *(available_message_create_time - capture_message_create_time) > threshold; cursor apply_reader_latency (threshold NUMBER) is select apply_name, 86400 *(dequeue_time - dequeued_message_create_time) latency from gv$streams_apply_reader where 86400 *(dequeue_time - dequeued_message_create_time) > threshold; cursor apply_lwm_latency (threshold NUMBER) is select r.apply_name, 86400 *(r.dequeue_time - c.lwm_message_create_time) latency from gv$streams_apply_reader r, gv$streams_apply_coordinator c where r.apply# = c.apply# and r.apply_name = c.apply_name and 86400 *(r.dequeue_time - c.lwm_message_create_time) > threshold; cursor queue_stats is select queue_schema, queue_name, num_msgs, spill_msgs, cnum_msgs, cspill_msgs, (cspill_msgs/DECODE(cnum_msgs, 0, 1, cnum_msgs) * 100) spill_ratio, 86400 *(sysdate - startup_time) alive from gv$buffered_queues; cursor logminer_spill_time (threshold NUMBER) is select c.capture_name, l.name, l.value from gv$streams_capture c, gv$logmnr_stats l where c.logminer_id = l.session_id and name = 'microsecs spent in pageout' and value > threshold; cursor complex_rule_sets_cap is select capture_name, owner, name from gv$rule_set r, dba_capture c where c.rule_set_owner = r.owner and c.rule_set_name = r.name and r.sql_executions > 0; cursor complex_rule_sets_prop is select propagation_name, owner, name from gv$rule_set r, dba_propagation p where p.rule_set_owner = r.owner and p.rule_set_name = r.name and r.sql_executions > 0; cursor complex_rule_sets_apply is select apply_name, owner, name from gv$rule_set r, dba_apply a where a.rule_set_owner = r.owner and a.rule_set_name = r.name and r.sql_executions > 0; cursor client_slow is select c.capture_name, c.state,l.available_txn-l.delivered_txn difference from gv$goldengate_capture c, gv$logmnr_session l where c.capture_name = l.session_name and c.state in (NULL, 'WAITING FOR CLIENT REQUESTS', 'WAITING FOR TRANSACTION;WAITING FOR CLIENT'); begin for rec in client_slow loop dbms_output.put_line('+ WARNING: Client is slow to request changes ('||rec.difference||' chunks available) from capture '||rec.capture_name); dbms_output.put_line('+ Investigate why the client application is slow'); slow_clients := TRUE; end loop; if slow_clients then dbms_output.put_line('+ The WAITING FOR CLIENT REQUESTS state is an indicator to investigate the client application rather than the capture server when there are chunks available from capture.'); dbms_output.put_line('+'); end if; for rec in capture_latency(capture_latency_threshold) loop dbms_output.put_line('+ WARNING: The latency of the Capture process ''' || rec.capture_name || ''' is ' || to_char(rec.latency, '99999999') || ' seconds!'); if verbose then dbms_output.put_line('+ This measurement shows how far behind the Capture process is in processing the'); dbms_output.put_line('+ redo log. This may be due to slowdown in any of the common Streams components:'); dbms_output.put_line('+ Capture, Propagation, and/or Apply. If this latency is chronic and not due'); dbms_output.put_line('+ to errors, consider the above suggestions for improving Capture, Propagation,'); dbms_output.put_line('+ and Apply performance.'); dbms_output.put_line('+'); end if; end loop; for rec in apply_reader_latency(applyrdr_latency_threshold) loop dbms_output.put_line('+ WARNING: The latency of the reader process for Apply ''' || rec.apply_name || ''' is ' || to_char(rec.latency, '99999999') || ' seconds!'); if verbose then dbms_output.put_line('+ This measurement shows how far behind the Apply reader is from when the message was'); dbms_output.put_line('+ created, which in the normal case is by a Capture process. In other words, '); dbms_output.put_line('+ the time between message creation and message dequeue by the Apply reader is too large.'); dbms_output.put_line('+ If this latency is chronic and not due to errors, consider the above suggestions '); dbms_output.put_line('+ for improving Capture and Propagation performance.'); dbms_output.put_line('+'); end if; end loop; for rec in apply_lwm_latency(applylwm_latency_threshold) loop dbms_output.put_line('+ WARNING: The latency of the coordinator process for Apply ''' || rec.apply_name || ''' is ' || to_char(rec.latency, '99999999') || ' seconds!'); if verbose then dbms_output.put_line('+ This measurement shows how far behind the low-watermark of the Apply process is'); dbms_output.put_line('+ from when the message was first created, which in the normal case is by a Capture process.'); dbms_output.put_line('+ The low-watermark is the most recent transaction (in terms of SCN) that has been'); dbms_output.put_line('+ successfully applied, for which all previous transactions have also been applied.'); dbms_output.put_line('+ A high latency can be due to long-running tranactions, many dependent transactions,'); dbms_output.put_line('+ or slow Capture, Propagation, or Apply processes.'); dbms_output.put_line('+'); end if; end loop; -- check queue performance for rec in queue_stats loop if rec.num_msgs > unconsumed_msgs_threshold then dbms_output.put_line('+ WARNING: There are ' || rec.num_msgs || ' unconsumed messages in queue ''' || rec.queue_schema || '''.''' || rec.queue_name || '''!'); dbms_output.put_line('+'); end if; if rec.spill_ratio > spill_ratio_threshold and rec.alive > spill_startup_threshold then dbms_output.put_line('+ WARNING: The queue ''' || rec.queue_schema || '''.''' || rec.queue_name || ''' has spilled ' || round(rec.spill_ratio) || '% of its messages!'); if verbose then dbms_output.put_line('+ Since the queue has been started, some large ratio of messages '); dbms_output.put_line('+ have been spilled to disk. If no errors have occurred which might '); dbms_output.put_line('+ have caused the spills in the past (such as an aborted Apply or'); dbms_output.put_line('+ Propagation process), and if you do not have long running transactions'); dbms_output.put_line('+ in your workload, consider increasing the size of the Streams Pool'); dbms_output.put_line('+ or increasing Apply parallelism.'); end if; dbms_output.put_line('+'); end if; /* if rec.cspill_msgs > cum_spilled_msgs_threshold then dbms_output.put_line('+ WARNING: There are ' || rec.cspill_msgs || ' cumulatively spilled messages in queue ''' || rec.queue_schema || '''.''' || rec.queue_name || '''!'); if verbose then dbms_output.put_line('+ Since the queue has been started, some large number of messages '); dbms_output.put_line('+ have been spilled to disk. If no errors have occurred which might '); dbms_output.put_line('+ have caused the spills in the past (such as an aborted Apply or'); dbms_output.put_line('+ Propagation process), and if you do not have long running transactions'); dbms_output.put_line('+ in your workload, consider increasing the size of the Streams Pool'); end if; dbms_output.put_line('+'); end if; */ end loop; -- logminer spill time for rec in logminer_spill_time(logminer_spill_threshold) loop dbms_output.put_line('+ WARNING: Excessive spill time for Capture process ''' || rec.capture_name || '''!'); if verbose then dbms_output.put_line('+ Spill time implies that the Logminer component used by Capture '); dbms_output.put_line('+ does not have enough memory allocated to it. This condition '); dbms_output.put_line('+ occurs when the system workload contains many DDLs and/or LOB'); dbms_output.put_line('+ transactions. Consider increasing the size of memory allocated to the'); dbms_output.put_line('+ Capture process by increasing the ''_SGA_SIZE'' Capture parameter.'); end if; dbms_output.put_line('+'); end loop; -- sql executions in rule sets for rec in complex_rule_sets_cap loop complex_rules := TRUE; dbms_output.put_line('+ WARNING: Complex rules exist for Capture process ''' || rec.capture_name || ' and rule set ''' || rec.owner || '''.''' || rec.name || '''!'); end loop; for rec in complex_rule_sets_prop loop complex_rules := TRUE; dbms_output.put_line('+ WARNING: Complex rules exist for Propagation process ''' || rec.propagation_name || ' and rule set ''' || rec.owner || '''.''' || rec.name || '''!'); end loop; for rec in complex_rule_sets_apply loop complex_rules := TRUE; dbms_output.put_line('+ WARNING: Complex rules exist for Apply process ''' || rec.apply_name || ' and rule set ''' || rec.owner || '''.''' || rec.name || '''!'); end loop; if verbose and complex_rules then dbms_output.put_line('+ Complex rules require SQL evaluations per message by a Streams '); dbms_output.put_line('+ process. This slows down performance and should be avoided '); dbms_output.put_line('+ if possible. Examine the rules in the rule set (for example'); dbms_output.put_line('+ by looking at DBA_RULE_SET_RULES and DBA_RULES) and avoid uses'); dbms_output.put_line('+ of the ''like'' operator and function/procedure calls in rule'); dbms_output.put_line('+ conditions unless absolutely necessary.'); end if; dbms_output.put_line('+'); end; / set numf 9999999999999999999 set pages 9999 col apply_database_link HEAD 'Database Link|for Remote|Apply' format a15 set feedback on prompt ============================================================================================ prompt prompt ++ DATABASE INFORMATION ++ COL MIN_LOG FORMAT A7 COL PK_LOG FORMAT A6 COL UI_LOG FORMAT A6 COL FK_LOG FORMAT A6 COL ALL_LOG FORMAT A6 COL FORCE_LOG FORMAT A10 col archive_change# format 999999999999999999 col archivelog_change# format 999999999999999999 COL NAME HEADING 'Name' col platform_name format a30 wrap col current_scn format 99999999999999999 SELECT DBid,name,created, SUPPLEMENTAL_LOG_DATA_MIN MIN_LOG,SUPPLEMENTAL_LOG_DATA_PK PK_LOG, SUPPLEMENTAL_LOG_DATA_UI UI_LOG, SUPPLEMENTAL_LOG_DATA_FK FK_LOG, SUPPLEMENTAL_LOG_DATA_ALL ALL_LOG, FORCE_LOGGING FORCE_LOG, resetlogs_time,log_mode, archive_change#, open_mode,database_role,archivelog_change# , current_scn, min_required_capture_change#, platform_id, platform_name from v$database; prompt ============================================================================================ prompt prompt ++ INSTANCE INFORMATION ++ col host format a20 wrap col blocked heading 'Blocked?' format a8 col shutdown_pending Heading 'Shutdown|Pending?' format a8 col parallel Heading 'Parallel' format a8 col archiver Heading 'Archiver' col active_state Heading 'Active|State' col instance heading 'Instance' col name heading 'Name' col host Heading 'Host' col version heading 'Version' col startup_time heading 'Startup|Time' col status Heading 'Status' col logins Heading 'Logins' col instance_role Heading 'Instance|Role' select instance_number INSTANCE, instance_name NAME, HOST_NAME HOST, VERSION, STARTUP_TIME, STATUS, PARALLEL, ARCHIVER, LOGINS, SHUTDOWN_PENDING, INSTANCE_ROLE, ACTIVE_STATE, BLOCKED from gv$instance; prompt prompt ============================================================================================ prompt +++ Current Database Incarnation +++ prompt col incarnation# HEADING 'Current|Incarnation' format 9999999999999999 col resetlogs_id HEADING 'ResetLogs|Id' format 9999999999999999 col resetlogs_change# HEADING 'ResetLogs|Change Number' format 9999999999999999 Select Incarnation#, resetlogs_id,resetlogs_change# from v$database_incarnation where status = 'CURRENT'; prompt ============================================================================================ prompt prompt ++ REGISTRY INFORMATION ++ col comp_id format a10 wrap Head 'Comp_ID' col comp_name format a35 wrap Head 'Comp_Name' col version format a10 wrap Head Version col schema format a10 Head Schema col modified Head Modified select comp_id, comp_name,version,status,modified,schema from DBA_REGISTRY; prompt +++ REGISTRY HISTORY +++ prompt select * from dba_registry_history; prompt prompt ============================================================================================ prompt prompt ++ NLS DATABASE PARAMETERS ++ col parameter format a30 wrap col value format a30 wrap select * from NLS_DATABASE_PARAMETERS; prompt ============================================================================================ prompt prompt ++ GLOBAL NAME ++ select global_name from global_name; prompt prompt ============================================================================================ prompt prompt ++ Key Init.ORA parameters ++ prompt col name HEADING 'Parameter|Name' format a30 col value HEADING 'Parameter|Value' format a15 col description HEADING 'Description' format a60 word select name,value,description from v$parameter where name in ('aq_tm_processes', 'archive_lag_target', 'job_queue_processes','_job_queue_interval', 'shared_pool_size', 'sga_max_size', 'memory_max_target','memory_target', 'sga_target','streams_pool_size', 'global_names', 'compatible','log_parallelism', 'logmnr_max_persistent_sessions', 'processes', 'sessions' ); prompt prompt ============================================================================================ prompt prompt ++ Streams Administrator ++ column username heading 'Administrator|Name' column local_privileges Heading 'Local|Privileges' format a10 column access_from_remote Heading 'Remote|Access' format a10 select * from dba_streams_administrator; prompt prompt ============================================================================================ prompt prompt ++ STREAMS QUEUES IN DATABASE ++ prompt ========================================================================================== prompt COLUMN OWNER HEADING 'Owner' FORMAT A10 COLUMN NAME HEADING 'Queue Name' FORMAT A30 COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A30 COLUMN ENQUEUE_ENABLED HEADING 'Enqueue|Enabled' FORMAT A7 COLUMN DEQUEUE_ENABLED HEADING 'Dequeue|Enabled' FORMAT A7 COLUMN USER_COMMENT HEADING 'Comment' FORMAT A20 COLUMN PRIMARY_INSTANCE HEADING 'Primary|Instance|Owner'FORMAT 999999 column SECONDARY_INSTANCE HEADING 'Secondary|Instance|Owner' FORMAT 999999 COLUMN OWNER_INSTANCE HEADING 'Owner|Instance' FORMAT 999999 column NETWORK_NAME HEADING 'Network|Name' FORMAT A30 SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.enqueue_enabled, q.dequeue_enabled,t.primary_instance,t.secondary_instance, t.owner_instance,network_name, q.USER_COMMENT FROM DBA_QUEUES q, DBA_QUEUE_TABLES t WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND q.QUEUE_TABLE = t.QUEUE_TABLE AND q.OWNER = t.OWNER order by owner,queue_table,name; prompt prompt prompt +++ Queue Subscribers ++ prompt column protocol HEADING 'Protocol' column subscriber HEADING 'Subscriber' format a35 wrap column name HEADING 'Queue|Name' format a35 wrap column delivery_mode HEADING 'Delivery|Mode' format a23 column queue_to_queue HEADING 'Queue to|Queue' format a5 column protocol HEADING 'Protocol' SELECT qs.owner||'.'||qs.queue_name name, qs.queue_table, NVL2(qs.consumer_name,'CONSUMER: ','ADDRESS : ') || NVL(qs.consumer_name,qs.address) Subscriber, qs.delivery_mode,qs.queue_to_queue,qs.protocol FROM dba_queue_subscribers qs, dba_queue_tables qt WHERE qt.OBJECT_TYPE = 'SYS.ANYDATA' AND qs.QUEUE_TABLE = qt.QUEUE_TABLE AND qs.OWNER = qt.OWNER ORDER BY qs.owner,qs.queue_name; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt ========================================================================================= prompt prompt ++ Minimum Archive Log Necessary to Restart Capture ++ prompt Note: This query is valid for databases where the capture processes exist for the same source database. prompt set serveroutput on DECLARE hScn number := 0; lScn number := 0; sScn number; ascn number; alog varchar2(1000); begin select min(start_scn), min(applied_scn) into sScn, ascn from dba_capture; DBMS_OUTPUT.ENABLE(2000); for cr in (select distinct(a.ckpt_scn) from system.logmnr_restart_ckpt$ a where a.ckpt_scn <= ascn and a.valid = 1 and exists (select * from system.logmnr_log$ l where a.ckpt_scn between l.first_change# and l.next_change#) order by a.ckpt_scn desc) loop if (hScn = 0) then hScn := cr.ckpt_scn; else lScn := cr.ckpt_scn; exit; end if; end loop; if lScn = 0 then lScn := sScn; end if; dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:'); for cr in (select name, first_time from DBA_REGISTERED_ARCHIVED_LOG where lScn between first_scn and next_scn order by thread#) loop dbms_output.put_line(cr.name||' ('||cr.first_time||')'); end loop; end; / prompt prompt ++ Replication Bundle ++ prompt col name format A30 col value$ format A30 HEADing 'Bundled Patch version' select value$ from sys.props$ where name ='REPLICATION_BUNDLE'; prompt prompt ============================================================================================ prompt prompt ++ CAPTURE PROCESSES IN DATABASE ++ -- col start_scn format 9999999999999999 -- col applied_scn format 9999999999999999 col capture_name HEADING 'Capture|Name' format a30 wrap col status HEADING 'Status' format a10 wrap col QUEUE HEADING 'Queue' format a25 wrap col RSN HEADING 'Positive|Rule Set' format a25 wrap col RSN2 HEADING 'Negative|Rule Set' format a25 wrap col capture_type HEADING 'Capture|Type' format a10 wrap col error_message HEADING 'Capture|Error Message' format a60 word col logfile_assignment HEADING 'Logfile|Assignment' col checkpoint_retention_time HEADING 'Days to |Retain|Checkpoints' col Status_change_time HEADING 'Status|Timestamp' col error_number HEADING 'Error|Number' col version HEADING 'Version' col purpose HEADING 'Purpose' SELECT capture_name, queue_owner||'.'||queue_name QUEUE, capture_type, purpose,status, rule_set_owner||'.'||rule_set_name RSN, negative_rule_set_owner||'.'||negative_rule_set_name RSN2, checkpoint_retention_time, version, logfile_assignment,error_number, status_change_time, error_message FROM DBA_CAPTURE order by capture_name; prompt ++ CAPTURE PROCESS SOURCE INFORMATION ++ col QUEUE HEADING 'Queue' format a25 wrap col RSN HEADING 'Positive|Rule Set' format a25 wrap col RSN2 HEADING 'Negative|Rule Set' format a25 wrap col capture_type HEADING 'Capture|Type' format a10 wrap col source_database HEADING 'Source|Database' format a30 wrap col first_scn HEADING 'First|SCN' col start_scn HEADING 'Start|SCN' col captured_scn HEADING 'Captured|SCN' col applied_scn HEADING 'Applied|SCN' col last_enqueued_scn HEADING 'Last|Enqueued|SCN' col required_checkpoint_scn HEADING 'Required|Checkpoint|SCN' col max_checkpoint_scn HEADING 'Maximum|Checkpoint|SCN' col source_dbid HEADING 'Source|Database|ID' col source_resetlogs_scn HEADING 'Source|ResetLogs|SCN' col logminer_id HEADING 'Logminer|Session|ID' col source_resetlogs_time HEADING 'Source|ResetLogs|Time' SELECT capture_name, capture_type, source_database, captured_scn, applied_scn, last_enqueued_scn, required_checkpoint_scn, max_checkpoint_scn, first_scn, start_scn ||' ('||start_time||') ' start_scn, source_dbid, source_resetlogs_scn, source_resetlogs_time, logminer_id FROM DBA_CAPTURE order by capture_name; prompt Return to Summary prompt prompt ++ Non-Default CAPTURE PROCESS PARAMETERS ++ col CAPTURE_NAME HEADING 'Capture|Name' format a30 wrap col parameter HEADING 'Parameter|Name' format a28 col value HEADING 'Parameter|Value' format a20 col set_by_user HEADING 'Usr|Set?' format a3 -- break on capture_name select * from dba_capture_parameters where set_by_user='YES' order by capture_name,PARAMETER; prompt Return to Summary prompt ============================================================================================ prompt prompt ++ STREAMS CAPTURE RULES CONFIGURED WITH DBMS_STREAMS_ADM PACKAGE ++ col NAME Heading 'Capture|Name' format a25 wrap col object format a45 wrap heading 'Object' col source_database format a15 wrap col rule_set_type heading 'Rule Set|Type' col RULE format a45 wrap heading 'Rule |Name' col TYPE format a15 wrap heading 'Rule |Type' col dml_condition format a40 wrap heading 'Rule|Condition' col include_tagged_lcr heading 'Tagged|LCRs?' format a7 col same_rule_condition Head 'Rule Condition|Same as Orig?' format a14 select streams_name NAME,schema_name||'.'||object_name OBJECT, rule_set_type, SOURCE_DATABASE, STREAMS_RULE_TYPE ||' '||Rule_type TYPE , INCLUDE_TAGGED_LCR, same_rule_condition, rule_owner||'.'||rule_name RULE from dba_streams_rules where streams_type ='CAPTURE' order by name,object, source_database, rule_set_type,rule; prompt ++ STREAMS TABLE SUBSETTING RULES ++ col NAME Heading 'Capture Name' format a25 wraP col object format A25 WRAP col source_database format a15 wrap col RULE format a35 wrap col TYPE format a15 wrap col dml_condition format a40 wrap select streams_name NAME,schema_name||'.'||object_name OBJECT, RULE_TYPE || 'TABLE RULE' TYPE, rule_owner||'.'||rule_name RULE, DML_CONDITION , SUBSETTING_OPERATION from dba_streams_rules where streams_type = 'CAPTURE' and (dml_condition is not null or subsetting_operation is not null); prompt prompt ++ CAPTURE RULES BY RULE SET ++ col capture_name format a25 wrap heading 'Capture|Name' col RULE_SET format a25 wrap heading 'Rule Set|Name' col RULE_NAME format a25 wrap heading 'Rule|Name' col condition format a50 wrap heading 'Rule|Condition' set long 4000 select c.capture_name, rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, DBA_RULES r ,DBA_CAPTURE c where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rsr.rule_set_owner=c.rule_set_owner and rsr.rule_set_name=c.rule_set_name and rsr.rule_set_name in (select rule_set_name from dba_capture) order by rsr.rule_set_owner,rsr.rule_set_name; prompt +** CAPTURE RULES IN NEGATIVE RULE SET **+ prompt select c.capture_name, rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, DBA_RULES r ,DBA_CAPTURE c where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rsr.rule_set_owner=c.negative_rule_set_owner and rsr.rule_set_name=c.negative_rule_set_name and rsr.rule_set_name in (select negative_rule_set_name rule_set_name from dba_capture) order by rsr.rule_set_owner,rsr.rule_set_name; prompt prompt ++ CAPTURE RULE TRANSFORMATIONS BY RULE SET ++ col RULE_SET format a25 wrap heading 'Rule Set|Name' col RULE_NAME format a25 wrap heading 'Rule|Name' col condition format a60 wrap heading 'Rule|Condition' set long 4000 col action_context_name format a32 wrap col action_context_value format a32 wrap select rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET , r.* from dba_rule_set_rules rsr, dba_streams_transformations r where r.rule_name = rsr.rule_name and r.rule_owner = rsr.rule_owner and rule_set_name in (select rule_set_name from dba_capture) order by rsr.rule_set_owner,rsr.rule_set_name, r.rule_owner, r.rule_name,transform_type desc, step_number, precedence; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ============================================================================================ prompt prompt ++ Registered Log Files for Capture ++ COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10 COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 999999 COLUMN NAME HEADING 'Archived Redo Log|File Name' format a35 column first_scn HEADING 'Archived Log|First SCN' COLUMN FIRST_TIME HEADING 'Archived Log Begin|Timestamp' column next_scn HEADING 'Archived Log|Last SCN' COLUMN NEXT_TIME HEADING 'Archived Log Last|Timestamp' COLUMN MODIFIED_TIME HEADING 'Archived Log|Registered Time' COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' format A6 COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' format A6 COLUMN PURGEABLE HEADING 'Purgeable|Archive|Log' format a9 SELECT r.CONSUMER_NAME, r.SOURCE_DATABASE, r.SEQUENCE#, r.NAME, r.first_scn, r.FIRST_TIME, r.next_scn, r.next_time, r.MODIFIED_TIME, r.DICTIONARY_BEGIN, r.DICTIONARY_END, r.purgeable FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME ORDER BY source_database, consumer_name, r.first_scn; prompt ============================================================================================ prompt prompt ++ CAPTURE EXTRA ATTRIBUTES ++ COLUMN CAPTURE_NAME HEADING 'Capture Process' FORMAT A30 COLUMN ATTRIBUTE_NAME HEADING 'Attribute Name' FORMAT A15 COLUMN INCLUDE HEADING 'Include Attribute in LCRs?' FORMAT A30 COLUMN ROW_ATTRIBUTE HEADING 'Row' format A3 COLUMN DDL_ATTRIBUTE Heading 'DDL' format A3 SELECT CAPTURE_NAME, ATTRIBUTE_NAME, ROW_ATTRIBUTE, DDL_ATTRIBUTE, INCLUDE FROM DBA_CAPTURE_EXTRA_ATTRIBUTES ORDER BY CAPTURE_NAME; prompt ============================================================================================ prompt prompt ++ TABLES PREPARED FOR CAPTURE ++ col table_owner format a30 HEADING 'Table|Owner' col table_name format a30 HEADING 'Table|Name' col timestamp heading 'Timestamp' col supplemental_log_data_pk HEADING 'PK|Logging' col supplemental_log_data_ui HEADING 'UI|Logging' col supplemental_log_data_fk HEADING 'FK|Logging' col supplemental_log_data_all HEADING 'All|Logging' select * from dba_capture_prepared_tables order by table_owner,table_name; prompt ++ SCHEMAS PREPARED FOR CAPTURE ++ col schema_name heading 'Schema|Name' select * from dba_capture_prepared_schemas order by schema_name; prompt ++ SCHEMAS PREPARED ALLKEY FOR GG CAPTURE ++ select * from SYS.LOGMNR$SCHEMA_ALLKEY_SUPLOG order by 1; prompt ++ DATABASE PREPARED FOR CAPTURE ++ select * from dba_capture_prepared_database; prompt ============================================================================================ prompt prompt ++ TABLES WITH SUPPLEMENTAL LOGGING ++ col OWNER format a30 wrap col table_name format a30 wrap select distinct owner,table_name from dba_log_groups order by 1,2; prompt prompt ++ TABLE LEVEL SUPPLEMENTAL LOG GROUPS ENABLED FOR CAPTURE ++ col object format a40 wrap col column_name format a30 wrap col log_group_name format a25 wrap select owner||'.'||table_name OBJECT, log_group_name, log_group_type, decode(always,'ALWAYS','Unconditional','CONDITIONAL','Conditional',NULL,'Conditional') ALWAYS, generated from dba_log_groups order by 1,2; prompt ++ SUPPLEMENTALLY LOGGED COLUMNS ++ col logging_property heading 'Logging|Property' format a9 select owner||'.'||table_name OBJECT, log_group_name, column_name,position,LOGGING_PROPERTY from dba_log_group_columns order by 1,2; prompt prompt ++ Sync Capture Processes ++ prompt select * from dba_sync_capture order by capture_name ; PROMPT PROMPT +** Sync Capture TABLES **+ select * from dba_sync_capture_tables order by 1,2; Prompt Prompt +** Sync Capture TABLE Rules **+ prompt If Object or Type is not populated, then a manually constructed rule has been added to the rule set prompt COLUMN STREAMS_NAME HEADING 'Synchronous|Capture Name' FORMAT A15 COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15 COLUMN SUBSETTING_OPERATION HEADING 'Subsetting|Operation' FORMAT A10 COLUMN ENABLED HEADING 'Enabled?' FORMAT A8 select streams_name NAME,schema_name||'.'||object_name OBJECT, rule_set_type, SOURCE_DATABASE, STREAMS_RULE_TYPE ||' '||Rule_type TYPE , INCLUDE_TAGGED_LCR, same_rule_condition, rule_owner||'.'||rule_name RULE from dba_streams_rules where streams_type ='SYNC_CAPTURE' order by name,object, source_database, rule_set_type,rule; prompt ++ SYNC CAPTURE TABLE SUBSETTING RULES ++ col NAME Heading 'Capture Name' format a25 wraP col object format A25 WRAP col source_database format a15 wrap col RULE format a35 wrap col TYPE format a15 wrap col dml_condition format a40 wrap select streams_name NAME,schema_name||'.'||object_name OBJECT, RULE_TYPE || 'TABLE RULE' TYPE, rule_owner||'.'||rule_name RULE, DML_CONDITION , SUBSETTING_OPERATION from dba_streams_rules where streams_type = 'SYNC_CAPTURE' and (dml_condition is not null or subsetting_operation is not null); prompt prompt ++ SYNC CAPTURE RULES BY RULE SET ++ prompt col capture_name format a25 wrap heading 'Capture|Name' col RULE_SET format a25 wrap heading 'Rule Set|Name' col RULE_NAME format a25 wrap heading 'Rule|Name' col condition format a50 wrap heading 'Rule|Condition' set long 4000 select c.capture_name, rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, DBA_RULES r ,DBA_SYNC_CAPTURE c where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rsr.rule_set_owner=c.rule_set_owner and rsr.rule_set_name=c.rule_set_name and rsr.rule_set_name in (select rule_set_name from dba_sync_capture) order by rsr.rule_set_owner,rsr.rule_set_name; prompt prompt ++ SYNC CAPTURE RULE TRANSFORMATIONS BY RULE SET ++ prompt col RULE_SET format a25 wrap heading 'Rule Set|Name' col RULE_NAME format a25 wrap heading 'Rule|Name' col condition format a60 wrap heading 'Rule|Condition' set long 4000 col action_context_name format a32 wrap col action_context_value format a32 wrap select rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET , r.* from dba_rule_set_rules rsr, dba_streams_transformations r where r.rule_name = rsr.rule_name and r.rule_owner = rsr.rule_owner and rule_set_name in (select rule_set_name from dba_sync_capture) order by rsr.rule_set_owner,rsr.rule_set_name, r.rule_owner, r.rule_name,transform_type desc, step_number, precedence; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ========================================================================================== prompt prompt ++ MESSAGING CLIENTS IN DATABASE ++ prompt ========================================================================================= prompt COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A25 COLUMN QUEUE_OWNER HEADING 'Queue Owner' FORMAT A10 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A20 COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A11 COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A11 SELECT STREAMS_NAME, QUEUE_OWNER, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME FROM DBA_STREAMS_MESSAGE_CONSUMERS order by queue_owner, queue_name,streams_name; prompt prompt ++ MESSAGE CLIENT NOTIFICATIONS ++ prompt COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A25 COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A10 COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A20 COLUMN NOTIFICATION_TYPE HEADING 'Notification|Type' FORMAT A15 COLUMN NOTIFICATION_ACTION HEADING 'Notification|Action' FORMAT A35 SELECT STREAMS_NAME, QUEUE_OWNER, QUEUE_NAME, NOTIFICATION_TYPE, NOTIFICATION_ACTION FROM DBA_STREAMS_MESSAGE_CONSUMERS WHERE NOTIFICATION_TYPE IS NOT NULL order by queue_owner,queue_name,streams_name; prompt prompt ========================================================================================== prompt prompt ++ PROPAGATIONS IN DATABASE ++ prompt ========================================================================================= prompt COLUMN 'Source Queue' FORMAT A39 COLUMN 'Destination Queue' FORMAT A39 COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A35 column queue_to_queue HEADING 'Q-2-Q' column acked_scn HEADING 'Acknowledged SCN' column error_message HEADING 'Error Message' column error_date HEADING 'Error Date' SELECT p.propagation_name, p.SOURCE_QUEUE_OWNER ||'.'|| p.SOURCE_QUEUE_NAME ||'@'|| g.GLOBAL_NAME "Source Queue", p.DESTINATION_QUEUE_OWNER ||'.'|| p.DESTINATION_QUEUE_NAME ||'@'|| p.DESTINATION_DBLINK "Destination Queue", p.queue_to_queue, p.status, p.acked_scn, p.error_date, p.error_message FROM DBA_PROPAGATION p, GLOBAL_NAME g; prompt prompt ++ PROPAGATION RULE SETS IN DATABASE ++ prompt COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A35 COLUMN Positive HEADING 'Positive|Rule Set' FORMAT A35 COLUMN Negative HEADING 'Negative|Rule Set' FORMAT A35 SELECT PROPAGATION_NAME, RULE_SET_OWNER||'.'||RULE_SET_NAME Positive, NEGATIVE_RULE_SET_OWNER||'.'||NEGATIVE_RULE_SET_NAME Negative FROM DBA_PROPAGATION; prompt ============================================================================================ prompt prompt ++ STREAMS PROPAGATION RULES CONFIGURED ++ col NAME Heading 'Name' format a25 wrap col PropNAME format a25 Heading 'Propagation Name' col object format a25 wrap col source_database format a15 wrap col RULE format a35 wrap col TYPE format a15 wrap col dml_condition format a40 wrap col same_rule_condition Head 'Rule Condition|Same as Orig?' format a14 select streams_name NAME,schema_name||'.'||object_name OBJECT, rule_set_type, SOURCE_DATABASE, STREAMS_RULE_TYPE ||' '||Rule_type TYPE , INCLUDE_TAGGED_LCR, same_rule_condition, rule_owner||'.'||rule_name RULE from dba_streams_rules where streams_type = 'PROPAGATION' order by name,object, source_database, rule_set_type,rule; prompt ++ STREAMS TABLE SUBSETTING RULES ++ col NAME format a25 wraP col object format A25 WRAP col source_database format a15 wrap col RULE format a35 wrap col TYPE format a15 wrap col dml_condition format a40 wrap select streams_name NAME,schema_name||'.'||object_name OBJECT, RULE_TYPE || 'TABLE RULE' TYPE, rule_owner||'.'||rule_name RULE, DML_CONDITION , SUBSETTING_OPERATION from dba_streams_rules where streams_type = 'PROPAGATION' and (dml_condition is not null or subsetting_operation is not null); prompt prompt ++ PROPAGATION RULES BY RULE SET ++ prompt col RULE_SET format a25 wrap col RULE_NAME format a25 wrap col condition format a60 wrap set long 4000 REM break on RULE_SET set long 4000 select rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, dba_rules r where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rule_set_name in (select rule_set_name from dba_propagation) order by rsr.rule_set_owner,rsr.rule_set_name; prompt +** PROPAGATION RULES IN NEGATIVE RULE SET **+ prompt select c.propagation_name, rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, DBA_RULES r ,DBA_PROPAGATION c where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rsr.rule_set_owner=c.negative_rule_set_owner and rsr.rule_set_name=c.negative_rule_set_name and rsr.rule_set_name in (select negative_rule_set_name rule_set_name from dba_propagation) order by rsr.rule_set_owner,rsr.rule_set_name; prompt prompt ++ PROPAGATION RULE TRANSFORMATIONS BY RULE SET ++ col RULE_SET format a25 wrap col RULE_NAME format a25 wrap col action_context_name format a32 wrap col action_context_value format a32 wrap REM break on RULE_SET select rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET , r.* from dba_rule_set_rules rsr, dba_streams_transformations r where r.rule_name = rsr.rule_name and r.rule_owner = rsr.rule_owner and rule_set_name in (select rule_set_name from dba_propagation) order by rsr.rule_set_owner,rsr.rule_set_name, r.rule_owner, r.rule_name,transform_type desc, step_number, precedence; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt prompt ============================================================================================ prompt prompt ++ APPLY INFORMATION ++ col apply_name format a25 wrap heading 'Apply|Name' col queue format a25 wrap heading 'Queue|Name' col apply_tag format a7 wrap heading 'Apply|Tag' col ruleset format a25 wrap heading 'Rule Set|Name' col apply_user format a15 wrap heading 'Apply|User' col apply_captured format a15 wrap heading 'Captured or|User Enqueued' col RSN HEADING 'Positive|Rule Set' format a25 wrap col RSN2 HEADING 'Negative|Rule Set' format a25 wrap col message_delivery_mode HEADING 'Message|Delivery' format a15 col apply_database_link HEADING 'Remote Apply|Database Link' format a25 wrap col purpose HEADING Purpose format a25 Select apply_name,purpose,status,queue_owner||'.'||queue_name QUEUE, DECODE(APPLY_CAPTURED, 'YES', 'Captured', 'NO', 'User-Enqueued') APPLY_CAPTURED, apply_user, apply_tag, rule_set_owner||'.'||rule_set_name RSN, negative_rule_set_owner||'.'||negative_rule_set_name RSN2, message_delivery_mode, apply_database_link from DBA_APPLY order by apply_name; prompt ++ APPLY PROCESS INFORMATION ++ col max_applied_message_number HEADING 'Maximum Applied|Message Number' col error_message HEADING 'Apply|Error Message' format a60 wrap select apply_name, max_applied_message_number,status, status_change_time,error_number, error_message from dba_apply order by apply_name; prompt Return to Summary prompt ++ APPLY PROCESS HANDLERS ++ select apply_name, ddl_handler, message_handler, precommit_handler from dba_apply order by apply_name; prompt prompt ++ Non-Default APPLY PROCESS PARAMETERS ++ col APPLY_NAME format a30 col parameter format a28 col value format a28 REM break on apply_name select * from dba_apply_parameters where set_by_user = 'YES' or parameter in ('PARALLELISM','MAX_PARALLELISM') order by apply_name,parameter; prompt ============================================================================================ prompt prompt ++ STREAMS APPLY RULES CONFIGURED ++ col NAME format a25 wrap heading 'Streams|Name' col object format a25 wrap heading 'Database|Object' col source_database format a15 wrap heading 'Source|Database' col RULE format a35 wrap heading 'Rule|Name' col RULE format a35 wrap heading 'Rule|Name' col TYPE format a15 wrap heading 'Rule|Type' col dml_condition format a40 wrap heading 'Rule|Condition' col same_rule_condition Head 'Rule Condition|Same as Orig?' format a14 select streams_name NAME,schema_name||'.'||object_name OBJECT, rule_set_type, SOURCE_DATABASE, STREAMS_RULE_TYPE ||' '||Rule_type TYPE , INCLUDE_TAGGED_LCR, same_rule_condition, rule_owner||'.'||rule_name RULE from dba_streams_rules where streams_type = 'APPLY' order by name,object, source_database, rule_set_type,rule; prompt ++ STREAMS TABLE SUBSETTING RULES ++ col NAME format a25 wraP col object format A25 WRAP col source_database format a15 wrap col RULE format a35 wrap col TYPE format a15 wrap col dml_condition format a40 wrap select streams_name NAME,schema_name||'.'||object_name OBJECT, RULE_TYPE || 'TABLE RULE' TYPE, rule_owner||'.'||rule_name RULE, DML_CONDITION , SUBSETTING_OPERATION from dba_streams_rules where streams_type = 'APPLY' and (dml_condition is not null or subsetting_operation is not null) order by 1,2,3; prompt prompt ++ APPLY Rules ++ prompt prompt ++ APPLY RULES BY RULE SET ++ col RULE_SET format a25 wrap col RULE_NAME format a25 wrap col condition format a60 wrap set long 4000 REM break on RULE_SET select rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, dba_rules r where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rule_set_name in (select rule_set_name from dba_apply) order by rsr.rule_set_owner,rsr.rule_set_name; prompt ++ APPLY RULES IN NEGATIVE RULE SET ++ prompt select c.apply_name, rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET ,rsr.rule_owner||'.'||rsr.rule_name RULE_NAME, r.rule_condition CONDITION from dba_rule_set_rules rsr, DBA_RULES r ,DBA_APPLY c where rsr.rule_name = r.rule_name and rsr.rule_owner = r.rule_owner and rsr.rule_set_owner=c.negative_rule_set_owner and rsr.rule_set_name=c.negative_rule_set_name and rsr.rule_set_name in (select negative_rule_set_name rule_set_name from dba_apply) order by rsr.rule_set_owner,rsr.rule_set_name; prompt prompt ++ APPLY RULE TRANSFORMATIONS BY RULE SET ++ col action_context_name format a32 wrap col action_context_value format a32 wrap col RULE_SET format a25 wrap col RULE_NAME format a25 wrap col condition format a60 wrap set long 4000 REM break on RULE_SET select rsr.rule_set_owner||'.'||rsr.rule_set_name RULE_SET , r.* from dba_rule_set_rules rsr, dba_streams_transformations r where r.rule_name = rsr.rule_name and r.rule_owner = rsr.rule_owner and rule_set_name in (select rule_set_name from dba_apply) order by rsr.rule_set_owner,rsr.rule_set_name, r.rule_owner, r.rule_name,transform_type desc, step_number, precedence; prompt prompt prompt ++ RULE TRANSFORMATIONS STATUS ++ col action_context_name format a32 wrap col action_context_value format a32 wrap head 'Transformation Name' col RULE_SET format a25 wrap col RULE_NAME format a25 wrap col condition format a60 wrap set long 4000 REM break on RULE_SET select ac.owner||'.'||ac.object_name ACTION_CONTEXT_VALUE, o.status, o.object_type, o.created, o.last_ddl_time from dba_objects o, (select distinct replace(substr(transform_function_name,1,instr(transform_function_name,'.',1,1)-1),'"') owner , replace(substr(transform_function_name,instr(transform_function_name,'.',-1,1)+1),'"') object_name from dba_streams_transform_function ) ac where o.owner = ac.owner and o.object_name = ac.object_name; prompt ============================================================================================ prompt prompt ++ APPLY HANDLERS ++ col apply_name format a25 wrap col message_handler format a25 wrap col ddl_handler format a25 wrap select apply_name, message_handler, ddl_handler from dba_apply where message_handler is not null or ddl_handler is not null; prompt prompt ++ APPLY DML HANDLERS ++ col object format a35 wrap col user_procedure HEADING 'User |Procedure' format a40 wrap col handler_name HEADING 'Stmt |Handler' format a40 wrap col dblink Heading 'Apply|DBLink' format a15 wrap col apply_database_link HEAD 'Database Link|for Remote|Apply' format a25 wrap col operation_name HEADING 'Operation|Name' format a13 col typ Heading 'Handler|Type' format a17 wrap col lob_assemble HEADING 'Assemble|Lob?' format a8 select object_owner||'.'||object_name OBJECT, operation_name , handler_type TYP, decode(assemble_lobs,'Y','Yes','N','No','UNKNOWN') lob_assemble, apply_name, user_procedure, handler_name, APPLY_Database_link from dba_apply_dml_handlers order by object_owner,object_name,apply_name; prompt prompt ++ DML STATEMENT HANDLER STATUS ++ prompt col handler_name format a40 wrap col handler_comment format a40 wrap select * from dba_streams_stmt_handlers order by 1; prompt prompt ** DML Statement Handler Statements ** prompt select * from dba_streams_stmts order by 1,2; prompt prompt ++ DML PROCEDURE HANDLER STATUS ++ prompt col user_procedure format a40 wrap select o.owner||'.'||o.object_name OBJECT, o.status,o.object_type,o.created, o.last_ddl_time from dba_objects o, (select distinct user_procedure from dba_apply_dml_handlers where user_procedure is not null) h where o.owner=replace(substr(h.user_procedure,1, instr(h.user_procedure,'.',1,1)-1),'"',null) and o.object_name = replace(substr(h.user_procedure,instr(h.user_procedure,'.',-1,1)+1),'"',null) order by 1; prompt prompt ++ Apply Change Handlers ++ prompt prompt Change handlers are created using the Streams MAINTAIN_CHANGE_TABLE procedure select * from dba_apply_change_handlers order by source_table_owner, source_table_name, change_table_owner, apply_name,change_table_name, handler_name; prompt ============================================================================================ prompt prompt ++ APPLY ENQUEUE directives ++ select * from dba_apply_enqueue; prompt prompt ++ APPLY EXECUTE directives ++ select * from dba_apply_execute; prompt prompt ++ Conflict Detection Control ++ prompt select * From dba_apply_table_columns order by 1,2,3; prompt ============================================================================================ prompt prompt ++ UPDATE CONFLICT RESOLUTION COLUMNS ++ col object format a25 wrap col method_name heading 'Method' format a12 col resolution_column heading 'Resolution|Column' format a13 col column heading 'Column Name' format a30 select object_owner||'.'||object_name object, method_name, resolution_column, column_name , apply_database_link from dba_apply_conflict_columns order by object_owner,object_name; prompt ============================================================================================ prompt prompt ++ KEY COLUMNS SET FOR APPLY ++ select * from dba_apply_key_columns order by 1,2; prompt ============================================================================================ prompt prompt ++ OBJECT DEPENDENCIES SET FOR APPLY ++ select * from dba_apply_object_dependencies; prompt ============================================================================================ prompt prompt ++ VALUE DEPENDENCIES SET FOR APPLY ++ select * from dba_apply_value_dependencies; prompt ++ APPLY PROGRESS ++ col oldest_message_number HEADING 'Oldest|Message|SCN' col apply_time HEADING 'Apply|Timestamp' select * from dba_apply_progress order by apply_name; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt ============================================================================================ prompt prompt ++ ERROR QUEUE ++ col source_commit_scn HEADING 'Source|Commit|Scn' col message_number HEADING 'Message in| Txn causing|Error' col message_count HEADING 'Total|Messages|in Txn' col local_transaction_id HEADING 'Local|Transaction| ID' col error_message HEADING 'Apply|Error|Message' col ERROR_CREATION_TIME HEADING 'Error|Creation|Timestamp' col source_transaction_id HEADING 'Source|Transaction| ID' Select apply_name, source_database,source_commit_scn, message_number, message_count, local_transaction_id, error_type, error_message , error_creation_time, source_transaction_id, source_commit_position from DBA_APPLY_ERROR order by apply_name ,source_commit_scn ; prompt ++ Tables by Error Type ++ prompt select m.error_number,object_owner,object_name, operation,count(*) from dba_apply_error_messages m group by m.error_number,object_owner,object_name,operation order by 1,2,3,4; prompt prompt ++ Failed txn PK for multiple transactions ++ prompt Shown only if PK column in view is not null (ie, populated) and count>1 prompt select m.error_number,object_owner,object_name, primary_key,count(*) from dba_apply_error_messages m where primary_key is not null having count(*) > 1 group by m.error_number,object_owner,object_name,primary_key order by 1,2,3; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ============================================================================================ prompt prompt ++ INSTANTIATION SCNs for APPLY TABLES ++ col source_database format a25 wrap col object HEADING 'Database|Object' format a45 col instantiation_scn format 9999999999999999 col apply_database_link HEAD 'Database Link|for Remote|Apply' format a25 wrap select source_database, source_object_owner||'.'||source_object_name OBJECT, ignore_scn, instantiation_scn, apply_database_link DBLINK from dba_apply_instantiated_objects order by source_database, object; prompt prompt ++ INSTANTIATION SCNs for APPLY SCHEMA and DATABASE (DDL) ++ col source_database HEADING 'Source|Database' format a30 col OBJECT HEADING 'Database|Object' format a45 col DBLINK HEADING 'Database|Link' col inst_scn HEADING 'Instantiation|SCN' col global_flag HEADING 'Schema or |Database' select source_database, source_schema OBJECT, apply_database_link DBLINK, instantiation_Scn INST_SCN, 'SCHEMA' global_flag from dba_apply_instantiated_schemas UNION select source_database, '' OBJECT, apply_database_link DBLINK, instantiation_Scn INST_SCN, 'GLOBAL' global_flag from dba_apply_instantiated_global order by source_database,object; prompt prompt ================================================================================= prompt prompt ++ XSTREAM CONFIGURATION ++ prompt col source_database format a40 wrap Heading 'Source|Database' COLUMN SERVER_NAME HEADING 'Server|Name' COLUMN CAPTURE_NAME HEADING 'Capture|Process' COLUMN CAPTURE_USER HEADING 'Capture|User' COLUMN committed_data_only HEADING 'Committed|Data Only' COLUMN Start_scn Heading 'Start SCN' format 9999999999999999 COLUMN Connect_user Heading 'Connect|User' Column Create_date Heading 'Create|Date' Column Start_time Heading 'Start Time' column username heading 'Administrator|Name' column local_privileges Heading 'Local|Privileges' format a10 column access_from_remote Heading 'Remote|Access' format a10 column queue_owner Heading 'Queue|Owner' column queue_name Heading 'Queue|Name' column apply_user Heading 'Apply|User' column User_comment Heading 'User|Comment' prompt ++ XStream Administrator ++ select * from dba_xstream_administrator; select * from dba_goldengate_privileges; prompt prompt ++ XStream Outbound Servers ++ prompt Select SERVER_NAME, STATUS, CONNECT_USER, CAPTURE_NAME, SOURCE_DATABASE, START_SCN ||' ('|| START_TIME||')', CAPTURE_USER, QUEUE_OWNER, QUEUE_NAME, USER_COMMENT, CREATE_DATE, COMMITTED_DATA_ONLY from dba_xstream_outbound order by server_name; prompt prompt ++ XStream Inbound Servers ++ prompt select * from dba_xstream_inbound order by server_name; prompt prompt ++ XStream Rules ++ prompt col streams_name Head 'Name' col streams_type head 'Process|Type' col schema_name Head 'Schema|Name' col streams_rule_type Head 'Rule|Granularity' col rule_set_owner Head 'Rule Set|Owner' col rule_set_name Head 'Rule Set|Name' col rule_owner Head 'Rule|Owner' col rule_name Head 'Rule|Name' col Rule_set_type Head 'Rule Set|Type' col Rule_type head 'Rule|Type' col Rule_condition Head 'Rule|Condition' col object_name Head 'Object|Name' col dml_condition Head 'DML|Condition' col original_rule_condition Head 'Orig Rule|Condition' col same_rule_condition Head 'Rule Condition|Same as Orig?' format a14 col include_tagged_lcr Head 'Include|Tagged?' format a8 col subsetting_operation Head 'Subsetting|Operation' format a10 select * from dba_xstream_rules order by streams_name, streams_type desc, streams_rule_type asc,schema_name,object_name; prompt prompt ** DML Conflict Handlers Details ** prompt select * from DBA_APPLY_DML_CONF_HANDLERS order by 1,2,3,4,5,6; prompt prompt ** Handle Collisions Details ** prompt select * from DBA_APPLY_HANDLE_COLLISIONS order by 1,2,3,4; prompt prompt ** Reperror Handlers Details ** prompt select * from DBA_APPLY_REPERROR_HANDLERS order by 1,2,3,4,5,6; prompt prompt prompt ++ XSTREAM Outbound Progress Table ++ prompt col processed_low_position format a40 wrap col applied_low_position format a40 wrap col applied_high_position format a40 wrap col spill_position format a40 wrap select * From dba_xstream_outbound_progress order by server_name; prompt prompt ++ XSTREAM Inbound Progress Table ++ prompt select * From dba_xstream_inbound_progress order by server_name; prompt prompt ============================================================================================ prompt prompt ++ DBA OBJECTS - Rules, and Streams Processes ++ prompt col OBJECT format a45 wrap heading 'Object' select owner||'.'||object_name OBJECT, object_id,object_type,created,last_ddl_time, status from dba_objects WHERE object_type in ('RULE','RULE SET','CAPTURE','APPLY') order by object_type, object; prompt prompt ============================================================================================ prompt prompt ============================================================================================ prompt prompt ++ Check RECOVERABLE tables ++ prompt Automation from MAINTAIN_* scripts and SPLIT/MERGE jobs prompt set long 100 col progress format a28 select sysdate,rs.creation_time, rs.invoking_package||'.'||rs.invoking_procedure PROCEDURE, rs.status, rs.done_block_num||' of '||rs.total_blocks||' Steps Completed' PROGRESS, to_number(sysdate-rs.creation_time)*86400 ELAPSED_SECONDS, rs.script_id, rsb.forward_block CURRENT_STEP from dba_recoverable_script rs, dba_recoverable_script_blocks rsb where rs.script_id = rsb.script_id and rsb.block_num = rs.done_block_num + 1; prompt prompt ++ Check RECOVERABLE ERROR view ++ prompt SELECT e.* FROM DBA_RECOVERABLE_SCRIPT_ERRORS e, dba_recoverable_script s where e.script_id = s.script_id order by e.script_id; prompt prompt ++ Identify Current Script Blocks ++ prompt set long 4000 select b.script_id, b.block_num, b.status, forward_block_dblink,forward_block from dba_recoverable_script_blocks b, dba_recoverable_script s where b.script_id =s.script_id order by b.script_id, block_num ; prompt prompt ++ History of Recoverable Scripts in last 30 days ++ prompt set long 4000 select rs.creation_time, rs.invoking_package||'.'||rs.invoking_procedure PROCEDURE, rs.status, rs.done_block_num||' of '||rs.total_blocks||' Steps Completed' PROGRESS, rs.script_id, rs.script_comment from dba_recoverable_script_hist rs where sysdate-creation_time < 30 order by creation_time; prompt prompt ++ Recoverable Script Parameters ++ prompt set long 4000 select * from dba_recoverable_script_params order by 1,2,3; prompt prompt ++ Defined Comparisons ++ prompt select * from dba_comparison order by owner,comparison_name,comparison_mode; prompt prompt ++ Comparison Information ++ prompt select * From dba_comparison_scan order by owner,comparison_name,parent_scan_id,scan_id; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ++ History ++ prompt col snap_id format 999999 HEADING 'Snap ID' col BEGIN_INTERVAL_TIME format a28 HEADING 'Interval|Begin|Time' col END_INTERVAL_TIME format a28 HEADING 'Interval|End|Time' col INSTANCE_NUMBER HEADING 'Instance|Number' col Queue format a28 wrap Heading 'Queue|Name' col num_msgs HEADING 'Current|Number of Msgs|in Queue' col cnum_msgs HEADING 'Cumulative|Total Msgs|for Queue' col spill_msgs HEADING 'Current|Spilled Msgs|in Queue' col cspill_msgs HEADING 'Cumulative|Total Spilled|for Queue' col dbid HEADING 'Database|Identifier' col total_spilled_msg HEADING 'Cumulative|Total Spilled|Messages' prompt prompt ++ Streams Buffered Queue History for last day ++ select s.begin_interval_time,s.end_interval_time , bq.snap_id, bq.num_msgs, bq.spill_msgs, bq.cnum_msgs, bq.cspill_msgs, bq.queue_schema||'.'||bq.queue_name Queue, bq.queue_id, bq.startup_time,bq.instance_number,bq.dbid from dba_hist_buffered_queues bq, dba_hist_snapshot s where bq.snap_id=s.snap_id and s.end_interval_time >= systimestamp-1 order by bq.queue_schema,bq.queue_name,s.end_interval_time; prompt prompt ++ Streams Buffered Subscriber History for last day ++ select s.begin_interval_time,s.end_interval_time , bs.snap_id,bs.subscriber_id, bs.num_msgs, bs.cnum_msgs, bs.total_spilled_msg, bs.subscriber_name,subscriber_address, bs.queue_schema||'.'||bs.queue_name Queue, bs.startup_time,bs.instance_number,bs.dbid from dba_hist_buffered_subscribers bs, dba_hist_snapshot s where bs.snap_id=s.snap_id and s.end_interval_time >= systimestamp-1 order by bs.queue_schema,bs.queue_name,bs.subscriber_id,s.end_interval_time; prompt prompt ++ Streams Capture History for last day ++ column total_messages_created HEADING 'Total|Messages|Created' column total_messages_enqueued HEADING 'Total Messages|Enqueued' column lag HEADING 'Capture|Lag|(Seconds)' column elapsed_capture HEADING 'Elapsed Time|Capture|(centisecs' column elapsed_rule_time HEADING 'Elapsed Time|Rule Evaluation|(centisecs)' column elapsed_enqueue_time HEADING 'Elapsed Time|Enqueuing Messages|(centisecs)' column elapsed_lcr HEADING 'Elapsed Time|LCR Creation|(centisecs)' column elapsed_redo_wait_time HEADING 'Elapsed Time|Redo Wait|(centisecs)' column elapsed_Pause_time HEADING 'Elapsed Time|Paused|(centisecs)' select s.begin_interval_time,s.end_interval_time , sc.capture_name,sc.lag, sc.total_messages_captured,sc.total_messages_enqueued, sc.elapsed_pause_time, sc.elapsed_redo_wait_time, sc.elapsed_rule_time, sc.elapsed_enqueue_time, sc.startup_time,sc.instance_number,sc.dbid from dba_hist_streams_capture sc, dba_hist_snapshot s where sc.snap_id=s.snap_id and s.end_interval_time >= systimestamp-1 order by sc.capture_name,s.end_interval_time; prompt prompt ++ Streams Apply History for last day ++ col reader_total_messages_dequeued HEADING 'Reader|Total Msgs|Dequeued' col reader_lag HEADING 'Reader|Lag|(Seconds)' col coord_total_received HEADING 'Coordinator|Total Txn|Received' col coord_total_applied HEADING 'Coordinator|Total Txn|Applied' col coord_total_rollbacks HEADING 'Coordinator|Total Txn|Rollbacks' col coord_total_wait_deps HEADING 'Coordinator|Total Txn|Wait-Dep' col coord_total_wait_cmts HEADING 'Coordinator|Total Txn|Wait-Cmt' col coord_lwm_lag HEADING 'Coordinator|LWM Lag|(seconds)' col server_total_messages_applied HEADING 'Server|Total Msgs|Applied' col server_elapsed_dequeue_time HEADING 'Server|Elapsed Dequeue|Time (cs)' col server_elapsed_apply_time HEADING 'Server|Elapsed Apply|Time (cs)' select s.begin_interval_time,s.end_interval_time , sa.apply_name,sa.reader_lag, sa.reader_total_messages_dequeued, sa.coord_lwm_lag, sa.coord_total_received,sa.coord_total_applied, sa.coord_total_rollbacks, sa.coord_total_wait_deps,sa.coord_total_wait_cmts, sa.server_total_messages_applied, sa.server_elapsed_dequeue_time, sa.server_elapsed_apply_time, sa.startup_time,sa.instance_number,sa.dbid from dba_hist_streams_apply_sum sa, dba_hist_snapshot s where sa.snap_id=s.snap_id and s.end_interval_time >= systimestamp-1 order by sa.apply_name,s.end_interval_time; prompt prompt ++ SUSPICIOUS RULES ++ prompt col object format a45 wrap col rule format a45 wrap REM This script does sanity checking of STREAMS objects compared to the underlying RULES. REM It is assumed that the DBMS_STREAMS_ADM package procedures REM ADD_TABLE_RULES REM ADD_SCHEMA_RULES REM ADD_GLOBAL_RULES REM have been used to configure streams. prompt prompt ++ Check for MISSING RULES IN DBA_RULES ++ prompt . Rows are returned if a rule is defined in DBA_STREAMS_TABLE_RULES (or SCHEMA, GLOBAL, too) prompt . but does not exist in the DBA_RULES view. prompt select rule_owner,rule_name from dba_streams_rules MINUS select rule_owner,rule_name from dba_rules; prompt prompt ++ Check for EXTRA RULES IN DBA_RULES ++ prompt . Rows are returned if a rule is defined in the DBA_RULES view prompt . but does not exist in the DBA_STREAMS_RULES view. prompt col rule_name format a30 select rule_owner,rule_name from dba_rules MINUS select rule_owner,rule_name from dba_streams_rules; prompt prompt ++ Check for RULE_CONDITIONS DO NOT MATCH BETWEEN STREAMS AND RULES ++ prompt . Rows are returned if the rule condition is different between the DBA_STREAMS_TABLE_RULES view prompt . and the DBA_RULES view. This indicates that a manual modification has been performed on the prompt . underlying rule. DBA_STREAMS_TABLE_RULES always shows the initial configuration rule condition. prompt select s.streams_type, s.streams_name, r.rule_owner||'.'||r.rule_name RULE,r.rule_condition from dba_streams_rules s, dba_rules r where r.rule_name=s.rule_name and r.rule_owner=s.rule_owner and dbms_lob.substr(s.rule_condition) != dbms_lob.substr(r.rule_condition); prompt prompt ++ Check for SOURCE DATABASE NAME DOES NOT MATCH FOR CAPTURE OR PROPAGATION RULES ++ prompt . Rows are returned if the source database column in the DBA_STREAMS_ RULES view prompt . for capture and/or propagation defined at this site does not match the prompt . global_name of this site. For capture rules, the source database must match the global_name prompt . of database. For propagation rules, the source database name will typically be the prompt . global name of the database. In some cases, it may be correct to have a different source prompt . database name from the global name. For example, at an intermediate node between a source site prompt . and the ultimate target site OR when using a downstream capture configuration, the rule source database prompt . name field will be diferent from the local. global name of the intermediate site. prompt select streams_type, streams_name, r.rule_owner||'.'||r.rule_name RULE from dba_streams_rules r where source_database is not null and source_database != (select global_name from global_name) and streams_type in ('CAPTURE','PROPAGATION'); prompt prompt ++ Check for GLOBAL RULE FOR CAPTURE SPECIFIED BUT CONDITION NOT MODIFIED ++ rem - It is assumed that GLOBAL rules for CAPTURE must be modified because of the unsupported datatypes in 9iR2. prompt . Rows are returned if a global rule is defined in the DBA_STREAMS_GLOBAL_RULES view prompt . and the rule condition in the DBA_RULES view has not been modified. prompt . In 9iR2, the GLOBAL rule must be modified to eliminate any unsupported datatypes. For example, prompt . the streams administrator schema must be eliminated from the capture rules. Failure to do prompt . this will result in the abort of the capture process. select streams_name, r.rule_owner||'.'||r.rule_name RULE from dba_streams_rules s , dba_rules r where streams_type = 'CAPTURE' and rule_type='GLOBAL' and r.rule_name=s.rule_name and r.rule_owner=s.rule_owner and dbms_lob.substr(s.rule_condition) = dbms_lob.substr(r.rule_condition); prompt prompt ++ Check for No RULE SET DEFINED FOR CAPTURE ++ prompt Prompt Capture requires a rule set to be defined to assure that only supported datatypes are captured. prompt select capture_name, capture_type, source_database from dba_capture where rule_set_name is null and negative_rule_set_name is null; prompt prompt ++ Check for APPLY RULES WITH NO SOURCE DATABASE SPECIFIED ++ prompt . Rows are returned if no source database is specified in the DBA_STREAMS_TABLE_RULES prompt . (SCHEMA,GLOBAL) view. An apply process can perform transactions from a single source database. prompt . In a typical replication environment, the source database name must be specified. In the single prompt . site case where captured events from the source database are handled by an apply process on the prompt . same database, the source database column does not need to be specified. prompt select streams_name, s.rule_owner||'.'||s.rule_name RULE, s.schema_name||'.'|| s.object_name OBJECT from dba_streams_rules s, dba_rules r where s.streams_type = 'APPLY' and s.source_database is null and r.rule_name=s.rule_name and r.rule_owner=s.rule_owner and dbms_lob.substr(s.rule_condition) = dbms_lob.substr(r.rule_condition); prompt prompt ++ Check for SCHEMA RULES FOR NON_EXISTANT SCHEMA ++ select s.streams_type, s.streams_name, s.rule_owner||'.'||s.rule_name RULE, s.schema_name, ac.nvn_name ACTION_CONTEXT_NAME, ac.nvn_value.accessvarchar2() ACTION_CONTEXT_VALUE from dba_streams_rules s , dba_rules r, dba_users u, table(r.rule_action_context.actx_list) ac where s.schema_name is null and u.username=s.schema_name and r.rule_owner=s.rule_owner and r.rule_name = s.rule_name and ac.nvn_value.accessvarchar2() is null; prompt prompt ++ Fast Evaluation Rule Set Rules for queues ++ prompt col RULE_SET Heading 'Rule Set' format a25 wrap col RULE_NAME Heading 'Rule' format a25 wrap col condition Heading 'Rule Condition' format a60 wrap set long 4000 select rsr.rule_set_owner||'.'||rsr.rule_set_name Rule_set,rsr.rule_owner||'.'||rsr.rule_name rule, r.rule_condition from dba_rule_set_rules rsr , dba_rules r where rsr.rule_set_name like '%_R' and rsr.rule_name=r.rule_name and rsr.rule_owner=r.rule_owner order by rsr.rule_set_owner,rsr.rule_set_name,rsr.rule_owner,rsr.rule_name; prompt -- To improve time in getting constraint infocompute Stats on sys.APPLY$_SOURCE_OBJ ; SYS only -- analyze table SYS.APPLY$_SOURCE_OBJ compute statistics; prompt ++ Check for CONSTRAINTS ON TABLES CONFIGURED IN STREAMS ++ prompt col LAST_CHANGE format a11 word heading 'Last|Change' col search_condition format a25 wrap heading 'Search|Condition' col ref_constraint HEADING 'Reference|Constraint' format a62 col object format a62 col constraint_name format a30 select distinct object,constraint_name,constraint_type, status, LAST_CHANGE, rely, Ref_constraint from ( select c.owner||'.'||c.table_name object,c.constraint_name,c.constraint_type, status, LAST_CHANGE, rely,r_owner||'.'||r_constraint_name Ref_constraint from dba_constraints c,dba_capture_prepared_tables p where c.owner=p.table_owner and c.table_name=p.table_name and c.constraint_type in ('P','U','R') and constraint_name not like 'SYS_IOT%' UNION ALL select c.owner||'.'||c.table_name object,c.constraint_name,c.constraint_type, status, LAST_CHANGE, rely, r_owner||'.'||r_constraint_name Ref_constraint from dba_constraints c,dba_apply_instantiated_objects p where c.owner=p.source_object_owner and c.table_name=p.source_object_name and c.constraint_type in ('P','U','R') and constraint_name not like 'SYS_IOT%' order by object); prompt ++ List INDEXES on TABLES ++ col object format a40 HEADING 'Table' col index_name format a40 col funcidx_status format a10 col index_type format a10 col column_name format a30 select ic.table_owner||'.'||ic.table_name object, table_type, ic.column_name,i.uniqueness,i.index_type,funcidx_status from dba_indexes i, dba_apply_instantiated_objects p, dba_ind_columns ic where i.owner=p.source_object_owner and i.table_name=p.source_object_name and ic.index_owner= i.owner and ic.index_name = i.index_name order by i.owner, i.table_name; prompt prompt ++ List UNSUPPORTED TABLES IN STREAMS ++ prompt select * from dba_streams_unsupported; prompt prompt ++ XStream Out Support Mode ++ prompt Lists Streams unsupported tables that can be supported with XStream select * from DBA_XSTREAM_OUT_SUPPORT_MODE where support_mode = 'ID KEY'; prompt prompt ++ STREAMS DICTIONARY INFORMATION ++ prompt Capture processes defined on system prompt col queue format a30 wrap heading 'Queue|Name' col capture_name format a20 wrap heading 'Capture|Name' col capture# format 9999 heading 'Capture|Number' col ruleset format a30 wrap heading 'Positive|Rule Set' col ruleset2 format a30 wrap heading 'Negative|Rule Set' select capture_name,status,purpose, checkpoint_retention_time,logminer_id,capture_type,first_scn, required_checkpoint_scn from dba_capture order by capture_name; select capture_name,capture#,queue_owner||'.'||queue_name queue, version,first_scn, ruleset_owner||'.'||ruleset_name ruleset, negative_ruleset_owner||'.'||negative_ruleset_name ruleset2 from sys.streams$_capture_process; prompt prompt Apply processes defined on system prompt col apply_name format a20 wrap heading 'Apply|Name' col apply# format 9999 heading 'Apply|Number' select apply_name,status,purpose, apply_tag,apply_user,message_delivery_mode,error_number,error_message from dba_apply order by apply_name; select apply_name,apply#,queue_owner||'.'||queue_name queue, ruleset_owner||'.'||ruleset_name ruleset , negative_ruleset_owner||'.'||negative_ruleset_name ruleset2 from sys.streams$_apply_process order by apply_name; prompt prompt Propagations defined on system prompt col source_queue format a30 wrap heading 'Queue|Name' col destination format a35 wrap heading 'Destination' select source_queue_schema||'.'||source_queue source_queue, destination_queue_schema||'.'||destination_queue||'@'|| destination_dblink destination, ruleset_schema||'.'||ruleset ruleset, negative_ruleset_schema||'.'||negative_ruleset ruleset2 from sys.STREAMS$_PROPAGATION_PROCESS; prompt prompt Streams rules defined on system prompt col nbr format 9999999999999999 heading 'Number of|Rules' col streams_name HEADING 'Streams Name' col streams_type HEADING 'Streams Type' select streams_name,streams_type,count(*) nbr From sys.streams$_rules group by streams_name,streams_type; prompt prompt ++ Streams sessions order by action prompt prompt SVR is server connection type: DED=DEDICATED; SHR=SHARED prompt col module format a30 wrap col action format a40 wrap col program format a30 col process format a15 wrap col SVR format a3 Heading 'SVR' select inst_id,logon_time,sid,serial#,module,action,process, program,status, decode(server,'DEDICATED','DED','SHR') SVR,event From gv$session where (module = 'GoldenGate' or module like '%tream%' or module like 'OGG%') order by inst_id,module,action; prompt prompt ++ Standby Redo Logs prompt ordered by thread#, sequence# select * from v$standby_log order by thread#,sequence#,first_change#; prompt order by first_change#, thread#, sequence# select * from v$standby_log order by first_change#,thread#,sequence#; prompt prompt prompt ++ prompt ++ LOGMINER DATABASE MAP ++ prompt Databases with information in logminer tables prompt col global_name format a30 wrap heading 'Global|Name' col logmnr_uid format 99999999 heading 'Logminer|Identifier'; col pdb_name format a30 wrap heading 'PDB|Name' select global_name,pdb_name,logmnr_uid, flags, logmnr_mdh from system.logmnrc_dbname_uid_map; prompt prompt Return to Summary prompt prompt LOGMNR_UID$ table prompt select * from system.logmnr_uid$; prompt prompt Return to Summary prompt prompt LOGMNR_SESSION$ table prompt prompt select * from system.logmnr_session$; prompt ++ LOGMINER PARAMETERS ++ REM select * from system.logmnr_parameter$; SELECT session#, type, scn, name, value FROM SYSTEM.logmnr_parameter$ ORDER BY session#, name; prompt prompt Return to Summary prompt prompt ++ LOGMINER STATISTICS ++ prompt select c.capture_name, name, value from gv$streams_capture c, gv$logmnr_stats l where c.logminer_id = l.session_id order by capture_name,name; prompt x$krvxsv stats (2times) col capture_name format a15 column name format a40 column value format a30 select x.inst_id, x.con_id, c.capture_name, x.name,x .value from x$krvxsv x, cdb_capture c where value != '0' and c.logminer_id=x.session_id and c.con_id = x.con_id order by capture_name, name; prompt select x.inst_id, x.con_id, c.capture_name, x.name,x .value from x$krvxsv x, cdb_capture c where value != '0' and c.logminer_id=x.session_id and c.con_id = x.con_id order by capture_name, name; prompt Return to Summary prompt ++ LOGMINER SESSION STATISTICS ++ prompt select * from gv$logmnr_session order by session_name; prompt REM prompt ordered by available_txn REM select session_name, USED_MEMORY_SIZE, AVAILABLE_TXN,DELIVERED_TXN, BUILDER_WORK_SIZE, PREPARED_WORK_SIZE from gv$logmnr_session order by available_txn; prompt prompt calculate difference, order by session_name SELECT sysdate, session_name,available_txn, delivered_txn, available_txn-delivered_txn as difference, max_memory_size, used_memory_size FROM gv$logmnr_session order by session_name; prompt prompt ++ LOGMINER CACHE OBJECTS ++ prompt Objects of interest to Streams from each source database prompt col count(*) format 9999999999999999 heading 'Number of|Interesting|DB Objects'; select logmnr_uid, count(*) from system.logmnrc_gtlo group by logmnr_uid; prompt prompt Intcol Verification prompt select logmnr_uid, obj#, objv#, intcol# from system.logmnrc_gtcs group by logmnr_uid, obj#, objv#, intcol# having count(1) > 1 order by 1,2,3,4; prompt prompt Segcol Verification prompt Check bug 7033630 if rows returned select a.logmnr_uid,a.obj#,a.objv#,a.segcol#, a.intcol# from system.logmnrc_gtcs a where exists ( select 1 from system.logmnrc_gtcs b where a.logmnr_uid = b.logmnr_uid and a.obj# = b.obj# and a.objv# = b.objv# and a.segcol# = b.segcol# and a.segcol# <> 0 and a.intcol# <> b.intcol#); prompt prompt ++ LCR Cache Information ++ prompt Internal LCRs select * from x$kngfl order by streams_name_kngfl,colcount_kngfl; prompt prompt External LCRs select * from x$kngfle order by streams_name_kngfl,colcount_kngfl; prompt prompt prompt ++ Streams Pool memory Information ++ prompt col name heading 'NAME' col value heading 'VALUE' select * from x$knlasg; prompt prompt ++ Cache statistics summary ++ prompt valid only if executed on instance running capture select CAPNAME_KNSTCAPCACHE as capture, CACHENAME_KNSTCAPCACHE as cache, NUM_LCRS_KNSTCAPCACHE as lcrs, NUM_COLS_KNSTCAPCACHE as cols, TOTAL_MEM_KNSTCAPCACHE/(1024*1024) as mem from x$knstcapcache order by 1,2; prompt prompt ++ Cache statistics ++ select * from x$knstcapcache; prompt prompt ++ Queue Memory and Flow Control Values ++ prompt FLCP_KWQBPMT is threshold for capture flow control prompt select * from x$kwqbpmt; prompt prompt ++ PGA Memory ++ prompt prompt col value format 999999999999999999 select * from gv$pgastat; prompt prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt Prompt ++ JOBS in Database ++ prompt set recsep each set recsepchar = select instance,job,what,log_user,priv_user,schema_user ,total_time,broken,interval,failures ,last_date,last_sec,this_date,this_sec,next_date,next_sec from dba_jobs; Prompt ++ Scheduler Jobs in Database ++ prompt select OWNER,JOB_NAME,JOB_SUBNAME,JOB_STYLE,JOB_CREATOR ,PROGRAM_OWNER,PROGRAM_NAME,JOB_TYPE,JOB_ACTION ,NUMBER_OF_ARGUMENTS ,SCHEDULE_OWNER,SCHEDULE_NAME,SCHEDULE_TYPE ,START_DATE,REPEAT_INTERVAL,END_DATE ,JOB_CLASS ,ENABLED ,AUTO_DROP ,RESTARTABLE ,STATE ,JOB_PRIORITY ,RUN_COUNT,MAX_RUNS,FAILURE_COUNT,MAX_FAILURES,RETRY_COUNT ,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE,SCHEDULE_LIMIT,MAX_RUN_DURATION ,LOGGING_LEVEL ,STOP_ON_WINDOW_CLOSE ,INSTANCE_STICKINESS ,RAISE_EVENTS ,SYSTEM ,JOB_WEIGHT ,SOURCE ,NUMBER_OF_DESTINATIONS ,DESTINATION_OWNER ,DESTINATION ,CREDENTIAL_OWNER ,CREDENTIAL_NAME ,INSTANCE_ID ,DEFERRED_DROP ,ALLOW_RUNS_IN_RESTRICTED_MODE from dba_scheduler_jobs; set recsep off prompt ++ DBA_QUEUE_SCHEDULES ++ prompt col start_time heading 'Start Time' col start_date Heading 'Start Date' col next_time heading 'Next Time' col propagation_window Heading 'Propagation|Window' select * from dba_queue_schedules order by schema,qname,destination,message_delivery_mode; prompt prompt prompt ++ Agents ++ prompt select * from dba_aq_agents; prompt prompt prompt ++ Agent Privileges ++ prompt select * from dba_aq_agent_privs; prompt prompt ++ Current Long Running Transactions ++ prompt Current Database transactions open for more than 20 minutes prompt col runlength HEAD 'Txn Open|Minutes' format 9999.99 col sid HEAD 'Session' format a13 col xid HEAD 'Transaction|ID' format a18 col terminal HEAD 'Terminal' format a10 col program HEAD 'Program' format a27 wrap select t.inst_id, sid||','||serial# sid,xidusn||'.'||xidslot||'.'||xidsqn xid, (sysdate - start_date ) * 1440 runlength ,terminal, program from gv$transaction t, gv$session s where t.addr=s.taddr and (sysdate - start_date) * 1440 > 20 order by runlength desc; prompt prompt ++ Streams Pool Advice ++ prompt This info is not populated in CCA-only environments prompt For CCA, see Streams Pool Statistics section col streams_pool_size_factor format 999.99 HEADING 'Stream Pool Size|Factor' col streams_pool_size_for_estimate HEADING 'Stream Pool Size|Estimate' col estd_spill_count HEADING 'Spill Count|Estimated' col estd_spill_time HEADING 'Spill Time |Estimated' col estd_unspill_count HEADING 'UnSpill Count|Estimated' col estd_unspill_time HEADING 'UnSpill Time |Estimated' select streams_pool_size_factor,streams_pool_size_for_estimate, estd_spill_count, estd_spill_time, estd_unspill_count, estd_unspill_time from v$streams_pool_advice; prompt prompt ++ Streams Pool Statistics ++ prompt col Total_memory_allocated Head 'Total MB Memory |Allocated' col current_size Head 'Streams Pool|Size' col SGA_TARGET_value Head 'SGA_TARGET|Value' col shrink_phase Head 'Shrink|Phase' col Advice_disabled Head 'Advice|Disabled' select * from gv$streams_pool_statistics; prompt select TOTAL_MEMORY_ALLOCATED/(1024*1024) as used_MB, CURRENT_SIZE/(1024*1024) as max_MB, decode(current_size, 0,to_number(null),(total_memory_allocated/current_size)*100) as pct_Streams_pool from gv$streams_pool_statistics; prompt prompt ++ Streams Pool Statistics for capture ++ set serveroutput on select capture_name,sga_used/(1024*1024) as used_MB, sga_allocated/(1024*1024) as alloced_MB, total_messages_captured as msgs_captured, total_messages_enqueued as msgs_enqueued from gv$streams_capture; prompt prompt ++ Memory Used by Logminer Sessions ++ select session_name, l.USED_MEMORY_SIZE/(1024*1024) as used_MB, l.MAX_MEMORY_SIZE/(1024*1024) as max_MB, (l.USED_MEMORY_SIZE/l.MAX_MEMORY_SIZE)*100 as pct_logminer_mem_used, decode(s.current_size, 0,to_number(null),(l.max_memory_size/s.current_size)*100) pct_streams_pool from gv$logmnr_session l, gv$streams_pool_statistics s where l.inst_id=s.inst_id order by session_name; prompt prompt ++ ALERTS History (10.2.0.3+) prompt prompt +++ Outstanding alerts prompt select message_type,creation_time,reason, suggested_action, module_id,object_type, instance_name||' (' ||instance_number||' )' Instance, time_suggested from dba_outstanding_alerts where creation_time >= sysdate -10 and rownum < 11 order by creation_time desc; prompt prompt +++ Most recent alerts(max=10) occuring within last 10 days +++ prompt column Instance Heading 'Instance Name|(Instance Number)' select message_Type,creation_time, reason,suggested_action, module_id,object_type, host_id, instance_name||' ( '||instance_number||' )' Instance, resolution,time_suggested from dba_alert_history where message_group in ('Streams','XStream','GoldenGate') and creation_time >= sysdate -10 and rownum < 11 order by creation_time desc; prompt prompt REM prompt ++ Current Contents of the STREAMS Pool ++ REM prompt Applies only to versions 10.1.0.4+, and to this instance only REM prompt Do not use this query - can cause database to hang or crash REM col comm HEAD 'Allocation Comment' format A18 REM col alloc_size HEAD 'Bytes Allocated' format 9999999999999999 REM select ksmchcom comm, sum(ksmchsiz) alloc_size from x$ksmsst group by ksmchcom order by 2 desc; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ++ init.ora parameters ++ Prompt Key parameters are aq_tm_processes, job_queue_processes prompt streams_pool_size, sga_max_size, global_name, compatible prompt col type heading 'TYPE' show parameters set serveroutput on prompt ++ STREAMS STATISTICS ++ prompt alter session set nls_date_format='YYYY-MM-DD HH24:Mi:SS'; set heading off set feedback off select 'STREAMS Health Check (&hcversion) for '||global_name||' on Instance='||instance_name||' generated: '||sysdate o from global_name, v$instance; set heading on set feedback on prompt ========================================================================================= prompt prompt ++ 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 order by 1; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt ============================================================================================ prompt prompt ++ CAPTURE STATISTICS ++ COLUMN PROCESS_NAME HEADING "Capture|Process|Number" FORMAT A7 COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A10 COLUMN SID HEADING 'Session|ID' FORMAT 99999999999999 COLUMN SERIAL# HEADING 'Session|Serial|Number' COLUMN STATE HEADING 'State' FORMAT A17 column STATE_CHANGED_TIME HEADING 'Last|State Change|Time' COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo Entries|Scanned' COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' COLUMN TOTAL_MESSAGES_CREATED HEADING 'Total|Messages|Created' COLUMN CAPTURE_TIME HEADING 'Capture Update|Timestamp' Column PURPOSE HEADING 'Capture|Purpose' column CCA Heading 'CCA?' column SGA_USED Heading 'Streams Pool|Used|MB' column SGA_ALLOCATED Heading 'Streams Pool| Allocated|MB' column BYTES_MINED Heading 'Redo|Mined|MB ' column SESSION_RESTART_SCN Heading 'SCN at |Startup' COLUMN LATENCY_SECONDS HEADING 'Latency|Seconds' FORMAT 9999999999999999 COLUMN CREATE_TIME HEADING 'Event Creation|Time' FORMAT A19 COLUMN ENQUEUE_TIME HEADING 'Last|Enqueue |Time' FORMAT A19 COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Last Queued|Message Number' FORMAT 9999999999999999 COLUMN ENQUEUE_MESSAGE_CREATE_TIME HEADING 'Last Queued|Message|Create Time'FORMAT A19 COLUMN CAPTURE_MESSAGE_CREATE_TIME HEADING 'Last Redo|Message|Create Time' FORMAT A19 COLUMN CAPTURE_MESSAGE_NUMBER HEADING 'Last Redo|Message Number' FORMAT 9999999999999999 COLUMN AVAILABLE_MESSAGE_CREATE_TIME HEADING 'Available|Message|Create Time' FORMAT A19 COLUMN AVAILABLE_MESSAGE_NUMBER HEADING 'Available|Message Number' FORMAT 9999999999999999 COLUMN STARTUP_TIME HEADING 'Startup Timestamp' FORMAT A19 COLUMN MSG_STATE HEADING 'Message State' FORMAT A13 COLUMN CONSUMER_NAME HEADING 'Consumer' FORMAT A30 COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A8 COLUMN START_DATE HEADING 'Start Date' COLUMN PROPAGATION_WINDOW HEADING 'Duration' FORMAT 99999 COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8 COLUMN LATENCY HEADING 'Latency|Seconds' FORMAT 99999999 -- ALTER session set nls_date_format='YYYY-MM-DD HH24:Mi:SS'; SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, c.CAPTURE_NAME, C.STARTUP_TIME, c.SID, c.SERIAL#, c.purpose, c.STATE, c.state_changed_time, DECODE(c.optimization,0,'NO','YES') cca, c.TOTAL_MESSAGES_CAPTURED, c.TOTAL_MESSAGES_ENQUEUED, total_messages_created, c.sga_used/1024/1024 sga_used, c.sga_allocated/1024/1024 sga_allocated, c.bytes_of_redo_mined/1024/1024 bytes_mined, c.session_restart_scn FROM gV$STREAMS_CAPTURE c, gV$SESSION s WHERE c.SID = s.SID AND c.SERIAL# = s.SERIAL# order by c.capture_name; SELECT capture_name, SYSDATE "Current Time", capture_time "Capture Process TS", capture_message_number, capture_message_create_time , enqueue_time , enqueue_message_number, enqueue_message_create_time , available_message_number, available_message_create_time FROM gV$STREAMS_CAPTURE order by capture_name; prompt Return to Summary COLUMN processed_scn HEADING 'Logminer Last|Processed Message' FORMAT 9999999999999999 COLUMN AVAILABLE_MESSAGE_NUMBER HEADING 'Last Message|Written to Redo' FORMAT 9999999999999999 SELECT c.capture_name, l.processed_scn, c.available_message_number FROM gV$LOGMNR_SESSION l, gv$STREAMS_CAPTURE c WHERE c.logminer_id = l.session_id order by c.capture_name; COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A15 COLUMN TOTAL_PREFILTER_DISCARDED HEADING 'Prefilter|Events|Discarded' FORMAT 9999999999999999 COLUMN TOTAL_PREFILTER_KEPT HEADING 'Prefilter|Events|Kept' FORMAT 9999999999999999 COLUMN TOTAL_PREFILTER_EVALUATIONS HEADING 'Prefilter|Evaluations' FORMAT 9999999999999999 COLUMN UNDECIDED HEADING 'Undecided|After|Prefilter' FORMAT 9999999999999999 COLUMN TOTAL_FULL_EVALUATIONS HEADING 'Full|Evaluations' FORMAT 9999999999999999 SELECT CAPTURE_NAME, TOTAL_PREFILTER_DISCARDED, TOTAL_PREFILTER_KEPT, TOTAL_PREFILTER_EVALUATIONS, (TOTAL_PREFILTER_EVALUATIONS - (TOTAL_PREFILTER_KEPT + TOTAL_PREFILTER_DISCARDED)) UNDECIDED, TOTAL_FULL_EVALUATIONS FROM gV$STREAMS_CAPTURE order by capture_name; column elapsed_capture HEADING 'Elapsed Time|Capture|(centisecs)' column elapsed_rule HEADING 'Elapsed Time|Rule Evaluation|(centisecs)' column elapsed_enqueue HEADING 'Elapsed Time|Enqueuing Messages|(centisecs)' column elapsed_lcr HEADING 'Elapsed Time|LCR Creation|(centisecs)' column elapsed_redo HEADING 'Elapsed Time|Redo Wait|(centisecs)' column elapsed_Pause HEADING 'Elapsed Time|Paused|(centisecs)' SELECT CAPTURE_NAME, ELAPSED_CAPTURE_TIME elapsed_capture, elapsed_rule_time elapsed_rule, ELAPSED_ENQUEUE_TIME elapsed_enqueue, ELAPSED_LCR_TIME elapsed_lcr, ELAPSED_REDO_WAIT_TIME elapsed_redo, ELAPSED_PAUSE_TIME elapsed_pause, total_messages_created, total_messages_enqueued, total_full_evaluations from gv$streams_capture order by capture_name; prompt prompt Total Memory (Allocated and Used) for Capture processing (capture+logminer) select capture_name, (c.sga_allocated + l.max_memory_size)/(1024*1024) Tot_MB_Alloc, (c.sga_used + l.used_memory_size)/(1024*1024) Tot_MB_Used from gv$streams_capture c, gv$logmnr_session l where c.capture_name = l.session_name order by c.capture_name; prompt Return to Summary prompt ========================================================================================= prompt prompt ++ Capture CCA Statistics ++ prompt CCA= Combined Capture and Apply automatic optimization prompt CCA is in effect if optimization > 0. prompt Apply message sent statistic info populated if appy is local to capture and there is a 1-1 prompt correspondence between capture and local apply. prompt Otherwise, see PROPAGATION Statistics section for message statistics prompt ========================================================================================= prompt Column APPLY_NAME HEADING 'Apply|Name' format a20 COLUMN APPLY_DBLINK HEADING 'Target|DbLink' format a30 COLUMN APPLY_MESSAGES_SENT Heading 'Msgs Sent|to Apply' COLUMN APPLY_BYTES_SENT HEADING 'Bytes Sent|to Apply' select capture_name,capture_time,state,optimization, apply_messages_sent,apply_bytes_sent, sid,serial#, apply_name,apply_dblink from gv$streams_capture order by capture_name; prompt ============================================================================================ prompt prompt ++ LOGMINER STATISTICS ++ prompt ++ (pageouts imply logminer spill) ++ COLUMN CAPTURE_NAME HEADING 'Capture|Name' FORMAT A32 COLUMN NAME HEADING 'Statistic' FORMAT A32 COLUMN VALUE HEADING 'Value' FORMAT 9999999999999999 select c.capture_name, name, value from gv$streams_capture c, gv$logmnr_stats l where c.logminer_id = l.session_id and name in ('bytes paged out', 'pageout time (seconds)', 'bytes of redo mined', 'bytes checkpointed', 'checkpoint time (seconds)', 'resume from low memory', 'distinct txns in queue' ) order by c.capture_name; prompt Return to Summary prompt prompt ++ BUFFERED PUBLISHERS ++ prompt prompt select * from gv$buffered_publishers; 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 +** Count of large transactions (lcrs>10000) **+ select streams_name, count(*) "Open Transactions",sum(cumulative_message_count) "Total LCRs" from gv$streams_transaction where streams_type='CAPTURE' and cumulative_message_count > 10000 group by streams_name; prompt prompt ++ OPEN STREAMS CAPTURE TRANSACTION DETAILS ++ select * from gv$streams_transaction where streams_type='CAPTURE' order by streams_name,first_message_number; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt ========================================================================== prompt prompt ++ XStream Outbound Server Statistics ++ prompt prompt ========================================================================== prompt prompt col sid HEADING 'Session id' col serial# HEADING 'Serial#' col state HEADING 'State' col spid HEADING 'Spid' col total_messages_sent HEADING 'Total|Messages|Sent' col Server_name HEADING 'Outbound|Server|Name' format a22 wrap col total_messages_sent heading 'Total|Messages|Sent' FORMAT 9999999999999999 col MESSAGE_SEQUENCE Heading 'Message within|Current transaction' col message_sequence FORMAT 9999999999999999 col last_sent_message_create_time HEADING 'Last Sent Message|Creation|Time' col last_sent_message_number HEADING 'Last Sent|Message|SCN' col last_sent_position HEADING 'Last Sent|Position' col commitscn Heading 'Source|Commit|SCN' col commit_position Heading 'Source|Commit|Position' col bytes_sent Heading 'Total Bytes|Sent' col committed_data_only Heading 'Committed|Data|Only' col startup_time Heading 'Server|Startup|Time' col elapsed_send_time HEADING 'Elapsed|Send|Time' col Send_time Heading 'Send Time' select * from gv$xstream_outbound_server order by server_name; prompt prompt ++ XSTREAM Outbound Progress Table ++ prompt col processed_low_position format a40 wrap col oldest_position format a40 wrap select * From dba_xstream_outbound_progress order by server_name; prompt Outbound apply progress col oldest_message_number HEADING 'Oldest|Message|SCN' col apply_time HEADING 'Apply|Timestamp' select ap.* from dba_apply_progress ap, dba_apply a where a.purpose ='XStream Out' and ap.apply_name=a.apply_name order by 1; prompt prompt prompt ========================================================================================= prompt prompt ++ SCHEDULE FOR EACH PROPAGATION ++ prompt prompt ========================================================================================= prompt COLUMN PROPAGATION_NAME Heading 'Propagation|Name' format a17 wrap COLUMN START_DATE HEADING 'Start Date' COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 9999999999999999 COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8 COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 9999999999999999 COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8 COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8 COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99 COLUMN LAST_ERROR_MSG HEADING 'Error Message' FORMAT A50 COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999999 COLUMN CURRENT_START_DATE HEADING 'Current|Start' FORMAT A17 COLUMN LAST_RUN_DATE HEADING 'Last|Run' FORMAT A17 COLUMN NEXT_RUN_DATE HEADING 'Next|Run' FORMAT A17 COLUMN LAST_ERROR_DATE HEADING 'Last|Error' FORMAT A17 column message_delivery_mode HEADING 'Message|Delivery|Mode' column queue_to_queue HEADING 'Q-2-Q' SELECT p.propagation_name,TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE, s.PROPAGATION_WINDOW, s.NEXT_TIME, s.LATENCY, DECODE(s.SCHEDULE_DISABLED, 'Y', 'Disabled', 'N', 'Enabled') SCHEDULE_DISABLED, s.PROCESS_NAME, s.total_bytes, s.FAILURES, s.message_delivery_mode, p.queue_to_queue, s.LAST_ERROR_MSG FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p WHERE p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 2), s.destination) AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME AND NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 1), s.destination)='"'||p.destination_queue_owner||'"."'||p.destination_queue_name||'"' order by message_delivery_mode, propagation_name; SELECT p.propagation_name, message_delivery_mode, TO_CHAR(s.LAST_RUN_DATE, 'HH24:MI:SS MM/DD/YY') LAST_RUN_DATE, TO_CHAR(s.CURRENT_START_DATE, 'HH24:MI:SS MM/DD/YY') CURRENT_START_DATE, TO_CHAR(s.NEXT_RUN_DATE, 'HH24:MI:SS MM/DD/YY') NEXT_RUN_DATE, TO_CHAR(s.LAST_ERROR_DATE, 'HH24:MI:SS MM/DD/YY') LAST_ERROR_DATE FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p WHERE p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 2), s.destination) AND s.SCHEMA = p.SOURCE_QUEUE_OWNER AND s.QNAME = p.SOURCE_QUEUE_NAME AND NVL(REGEXP_SUBSTR(s.destination, '[^@]+', 1, 1), s.destination)='"'||p.destination_queue_owner||'"."'||p.destination_queue_name||'"' order by message_delivery_mode, propagation_name; prompt prompt ++ EVENTS AND BYTES PROPAGATED FOR EACH PROPAGATION ++ prompt COLUMN Elapsed_propagation_TIME HEADING 'Elapsed |Propagation Time|(Seconds)' FORMAT 9999999999999999 COLUMN TOTAL_NUMBER HEADING 'Total |Events|Propagated' FORMAT 9999999999999999 COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999999 COLUMN SCHEDULE_STATUS HEADING 'Schedule|Status' column elapsed_dequeue_time HEADING 'Elapsed|Dequeue Time|(Seconds)' column elapsed_pickle_time HEADING 'Total Time|(Seconds)' column total_time HEADING 'Elapsed|Pickle Time|(Seconds)' column high_water_mark HEADING 'High|Water|Mark' column acknowledgement HEADING 'Target |Ack' SELECT p.propagation_name,q.message_delivery_mode, DECODE(p.STATUS, 'DISABLED', 'Disabled', 'ENABLED', 'Enabled') SCHEDULE_STATUS, q.instance, q.total_number TOTAL_NUMBER, q.TOTAL_BYTES , q.elapsed_dequeue_time/100 elapsed_dequeue_time, q.elapsed_pickle_time/100 elapsed_pickle_time, q.total_time/100 total_time FROM DBA_PROPAGATION p, dba_queue_schedules q WHERE p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(q.destination, '[^@]+', 1, 2), q.destination) AND q.SCHEMA = p.SOURCE_QUEUE_OWNER AND q.QNAME = p.SOURCE_QUEUE_NAME AND NVL(REGEXP_SUBSTR(q.destination, '[^@]+', 1, 1), q.destination)='"'||p.destination_queue_owner||'"."'||p.destination_queue_name||'"' order by q.message_delivery_mode, p.propagation_name; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt ++ PROPAGATION SENDER STATISTICS ++ prompt col queue_id HEADING 'Queue ID' col queue_schema HEADING 'Source|Queue|Owner' col queue_name HEADING 'Source|Queue|Name' col dst_queue_schema HEADING 'Destination|Queue|Owner' col dst_queue_name HEADING 'Destination|Queue|Name' col dblink Heading 'Destination|Database|Link' col total_msgs HEADING 'Total|Messages|Sent' col max_num_per_win HEADING 'Max Msgs|per|Window' col max_size HEADING 'Max|Size' col src_queue_schema HEADING 'Source|Queue|Owner' col src_queue_name HEADING 'Source|Queue|Name' column elapsed_dequeue_time HEADING 'Elapsed|Dequeue Time|(CentiSecs)' column elapsed_pickle_time HEADING 'Total Time|(CentiSecs)' column total_time HEADING 'Elapsed|Pickle Time|(CentiSecs)' col last_received_msg HEADING 'Last Received|Message|SCN' col last_received_msg_position HEADING 'Last Received|Message|Position' column acknowledgement HEADING 'Target |Acknowledgement|SCN' column acknowledgement_position HEADING 'Target |Acknowledgement|Position' col session_id HEADING 'Session_id' col serial# HEADING 'Serial#' col state HEADING 'State' col spid HEADING 'Spid' col elapsed_propagation_time HEADING 'Elapsed|Propagation|Time' col last_msg_latency HEADING 'Last Message|Latency' col last_msg_enqueue_time HEADING 'Last Message|Enqueue Time' col last_msg_propagation_time HEADING 'Last Message|Propagation Time' col last_lcr_latency HEADING 'Last LCR|Latency' col last_lcr_creation_time HEADING 'Last LCR|Creation Time' col last_lcr_propagation_time HEADING 'Last LCR|Propagation Time' col dst_database_name HEADING 'Destination|Database|Name' SELECT * from v$propagation_sender; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ++ PROPAGATION RECEIVER STATISTICS ++ prompt column src_queue_name HEADING 'Source|Queue|Name' column src_dbname HEADING 'Source|Database|Name' column startup_time HEADING 'Startup|Time' column elapsed_unpickle_time HEADING 'Elapsed|Unpickle Time|(CentiSeconds' column elapsed_rule_time HEADING 'Elapsed|Rule Time|(CentiSeconds)' column elapsed_enqueue_time HEADING 'Elapsed|Enqueue Time|(CentiSeconds)' col propagation_name HEADING 'Source|Propagation|Name' col last_received_msg_position HEADING 'Last Received|Message|Position' column acknowledgement HEADING 'Target |Acknowledgement|SCN' column acknowledgement_position HEADING 'Target |Acknowledgement|Position' col session_id HEADING 'Session_id' col serial# HEADING 'Serial#' col state HEADING 'State' col spid HEADING 'Spid' col total_msgs HEADING 'Total|Messages|Received' SELECT src_dbname,propagation_name, src_queue_schema,src_queue_name, dst_queue_schema,dst_queue_name, state, total_msgs,high_water_mark, acknowledgement,last_received_msg, session_id,serial#,spid,startup_time, elapsed_unpickle_time, elapsed_rule_time, elapsed_enqueue_time from gv$propagation_receiver order by src_dbname,propagation_name,src_queue_name,dst_queue_name; prompt prompt ++ BUFFERED SUBSCRIBERS ++ prompt prompt select * from gv$buffered_subscribers order by subscriber_name; prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt =========================================================================== prompt prompt ++ XStream Inbound Server Statistics ++ prompt prompt ============================================================================ prompt prompt ++ XStream IN Table Statistics summaries select con_id,apply_name, sum(total_inserts),sum(total_updates),sum(total_deletes), sum(insert_collisions),sum(update_collisions),sum(delete_collisions), sum(reperror_records),sum(reperror_ignores), sum(wait_dependencies), sum(cdr_insert_row_exists), sum(cdr_update_row_exists),sum(cdr_update_row_missing), sum(cdr_delete_row_exists),sum(cdr_delete_row_missing), sum(cdr_successful_resolutions),sum(cdr_failed_resolutions),sum(cdr_successful_resolutions+cdr_failed_resolutions) cdr_total_resolutions from gv$xstream_table_stats group by con_id, apply_name; prompt ++ XSTREAM IN TABLE STATISTICS by TABLE ++ prompt select con_id,apply_name, source_table_owner,source_table_name,destination_table_owner,destination_table_name, sum(total_inserts),sum(total_updates),sum(total_deletes), sum(insert_collisions),sum(update_collisions),sum(delete_collisions), sum(reperror_records),sum(reperror_ignores), sum(wait_dependencies), sum(cdr_insert_row_exists), sum(cdr_update_row_exists),sum(cdr_update_row_missing), sum(cdr_delete_row_exists),sum(cdr_delete_row_missing), sum(cdr_successful_resolutions),sum(cdr_failed_resolutions),sum(cdr_successful_resolutions+cdr_failed_resolutions) cdr_total_resolutions from gv$xstream_table_stats group by con_id,apply_name, server_id,source_table_owner,source_table_name,destination_table_owner,destination_table_name; prompt prompt prompt ** XSTREAM Inbound Progress Table ** prompt select * From dba_xstream_inbound_progress order by server_name; prompt prompt ============================================================================ prompt prompt ++ APPLY STATISTICS ++ prompt prompt ============================================================================================ prompt prompt ++ APPLY Reader Statistics ++ col oldest_scn_num HEADING 'Oldest|SCN' col apply_name HEADING 'Apply Name' col apply_captured HEADING 'Captured or|User-Enqueued LCRs' col process_name HEADING 'Process' col state HEADING 'STATE' col total_messages_dequeued HEADING 'Total Messages|Dequeued' col total_messages_spilled Heading 'Total Messages|Spilled' col sga_used HEADING 'SGA Used|MB' col sga_allocated HEADING 'SGA Allocated|MB' col oldest_transaction_id HEADING 'Oldest|Transaction' col total_lcrs_with_dep HEADING 'Total|LCRs with|Dependencies' col total_lcrs_with_wmdep HEADING 'Total|LCRs with|WM Dependency' col total_in_memory_lcrs HEADING 'Total|in-Memory|LCRs' col unassigned_complete_txns HEADING 'Unassigned|Complete|Txns' col auto_txn_buffer_size HEADING 'Auto|TXN Buffer|Size' SELECT ap.APPLY_NAME, DECODE(ap.APPLY_CAPTURED, 'YES','Captured LCRS', 'NO','User-Enqueued','UNKNOWN') APPLY_CAPTURED, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, r.STATE, r.TOTAL_MESSAGES_DEQUEUED, r.TOTAL_MESSAGES_SPILLED, r.SGA_USED/1024/1024 sga_used, r.sga_allocated/1024/1024 sga_allocated, oldest_scn_num, oldest_xidusn||'.'||oldest_xidslt||'.'||oldest_xidsqn oldest_transaction_id, total_lcrs_with_dep, total_lcrs_with_wmdep, total_in_memory_lcrs FROM gV$STREAMS_APPLY_READER r, gV$SESSION s, DBA_APPLY ap WHERE r.SID = s.SID AND r.SERIAL# = s.SERIAL# AND r.APPLY_NAME = ap.APPLY_NAME order by ap.apply_name; prompt Return to Summary col creation HEADING 'Dequeued Message|Creation|Timestamp' col last_dequeue HEADING 'Dequeue |Timestamp' col dequeued_message_number HEADING 'Last |Dequeued Message|Number' col last_browse_num HEADING 'Last|Browsed Message|Number' col latency HEADING 'Apply Reader|Latency|(Seconds)' SELECT APPLY_NAME, (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY, TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD') CREATION, TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD') LAST_DEQUEUE, DEQUEUED_MESSAGE_NUMBER, last_browse_num FROM gV$STREAMS_APPLY_READER order by apply_name; col elapsed_dequeue HEADING 'Elapsed Time|Dequeue|(centisecs)' col elapsed_schedule HEADING 'Elapsed Time|Schedule|(centisecs)' col elapsed_spill HEADING 'Elapsed Time|Spill|(centisecs)' col elapsed_idle HEADING 'Elapsed Time|Idle|(centisecs)' Select APPLY_NAME, total_messages_dequeued, total_messages_spilled, Elapsed_dequeue_time Elapsed_dequeue, elapsed_schedule_time elapsed_schedule, elapsed_spill_time elapsed_spill from gv$STREAMS_APPLY_READER order by apply_name; prompt Return to Summary prompt ========================================================================================= prompt prompt ++ Apply CCA Statistics ++ prompt ========================================================================================= prompt Column APPLY_NAME HEADING 'Apply|Name' COLUMN proxy_sid HEADING 'SID of |Apply Receiver' COLUMN proxy_serial HEADING 'Serial# of |Apply Receiver' column proxy_spid HEADING'OS PID of|Apply Receiver' format a14 COLUMN CAPTURE_BYTES_RECEIVED HEADING 'Bytes Recvd|From Capture' select apply_name,state,capture_bytes_received,proxy_spid, proxy_sid,proxy_serial from gv$streams_apply_reader order by apply_name; prompt ============================================================================================ prompt 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 order by apply_name; prompt ============================================================================================ prompt prompt ++ APPLY Coordinator Statistics ++ col apply_name HEADING 'Apply Name' format a22 wrap col process HEADING 'Process' format a7 col RECEIVED HEADING 'Total|Txns|Received' col ASSIGNED HEADING 'Total|Txns|Assigned' col APPLIED HEADING 'Total|Txns|Applied' col ERRORS HEADING 'Total|Txns|w/ Error' col total_ignored HEADING 'Total|Txns|Ignored' col total_rollbacks HEADING 'Total|Txns|Rollback' col WAIT_DEPS HEADING 'Total|Txns|Wait_Deps' col WAIT_COMMITS HEADING 'Total|Txns|Wait_Commits' col STATE HEADING 'State' format a10 word SELECT ap.APPLY_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS, c.STATE, c.TOTAL_RECEIVED RECEIVED, c.TOTAL_ASSIGNED ASSIGNED, c.TOTAL_APPLIED APPLIED, c.TOTAL_ERRORS ERRORS, c.total_ignored, c.total_rollbacks, c.TOTAL_WAIT_DEPS WAIT_DEPS, c.TOTAL_WAIT_COMMITS WAIT_COMMITS, c.unassigned_complete_txns, c.auto_txn_buffer_size FROM gV$STREAMS_APPLY_COORDINATOR c, gV$SESSION s, DBA_APPLY ap WHERE c.SID = s.SID AND c.SERIAL# = s.SERIAL# AND c.APPLY_NAME = ap.APPLY_NAME order by ap.apply_name; col lwm_msg_ts HEADING 'LWM Message|Creation|Timestamp' col lwm_msg_nbr HEADING 'LWM Message|SCN' col lwm_updated HEADING 'LWM Updated|Timestamp' col hwm_msg_ts HEADING 'HWM Message|Creation|Timestamp' col hwm_msg_nbr HEADING 'HWM Message|SCN' col hwm_updated HEADING 'HWM Updated|Timestamp' col LWM_POSITION HEADING 'XStream LWM|Position' col HWM_POSITION HEADING 'XStream HWM|Position' col PROCESSED_MESSAGE_NUMBER HEADING 'XStream Processed|Position' prompt Return to Summary SELECT APPLY_NAME, LWM_MESSAGE_CREATE_TIME LWM_MSG_TS , LWM_MESSAGE_NUMBER LWM_MSG_NBR , LWM_TIME LWM_UPDATED, HWM_MESSAGE_CREATE_TIME HWM_MSG_TS, HWM_MESSAGE_NUMBER HWM_MSG_NBR , HWM_TIME HWM_UPDATED, LWM_POSITION, HWM_POSITION, PROCESSED_MESSAGE_NUMBER FROM gV$STREAMS_APPLY_COORDINATOR; SELECT APPLY_NAME, TOTAL_RECEIVED,TOTAL_ASSIGNED,TOTAL_APPLIED, STARTUP_TIME, ELAPSED_SCHEDULE_TIME elapsed_schedule, ELAPSED_IDLE_TIME elapsed_idle from gv$streams_apply_coordinator order by apply_name; prompt ============================================================================================ prompt prompt ++ APPLY Server Statistics ++ col SRVR format 9999 col ASSIGNED format 9999999999999999 Heading 'Total|Transactions|Assigned' col MSG_APPLIED heading 'Total|Messages|Applied' FORMAT 9999999999999999 col MESSAGE_SEQUENCE FORMAT 9999999999999999 col applied_message_create_time HEADING 'Applied Message|Creation|Timestamp' col applied_message_number HEADING 'Last Applied|Message|SCN' col lwm_updated HEADING 'Applied|Timestamp' col message_sequence HEADING 'Message|Sequence' col elapsed_apply_time HEADING 'Elapsed|Apply|Time (cs)' col elapsed_dequeue_time HEADING 'Elapsed|Dequeue|Time (cs)' col apply_time Heading 'Apply Time' col total_lcrs_retried HEADING 'Total|LCRs|Retried' col total_txns_retried HEADING 'Total|TXNs|Retried' col total_txns_recorded HEADING 'Total|TXNs|Recorded' col lcr_retry_iteration HEADING 'LCR Retry|Iteration' col txn_retry_iteration HEADING 'TXN Retry|Iteration' col TOTAL_ASSIGNED format 9999999999999999 Heading 'Total|Transactions|Assigned' col TOTAL_MESSAGES_APPLIED heading 'Total|Messages|Applied' FORMAT 9999999999999999 col cnt HEADING 'Total|Apply|Servers' prompt prompt Apply Server TOTALs Summary prompt select apply_name, count(*) cnt, sum(a.total_assigned) total_assigned, sum(a.total_messages_applied) total_messages_applied, sum(a.total_lcrs_retried) total_lcrs_retried, sum(a.total_txns_retried) total_txns_retried, sum(a.total_txns_recorded) total_txns_recorded FROM gV$STREAMS_APPLY_SERVER a group by apply_name ; prompt prompt Apply Server Details SELECT ap.APPLY_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, a.server_id SRVR, a.STATE, a.TOTAL_ASSIGNED ASSIGNED, a.TOTAL_MESSAGES_APPLIED msg_APPLIED, a.APPLIED_MESSAGE_NUMBER, a.APPLIED_MESSAGE_CREATE_TIME , a.MESSAGE_SEQUENCE, a.lcr_retry_iteration, a.txn_retry_iteration, a.total_lcrs_retried, a.total_txns_retried, a.total_txns_recorded, a.elapsed_dequeue_time, a.elapsed_apply_time, a.apply_time FROM gV$STREAMS_APPLY_SERVER a, gV$SESSION s, DBA_APPLY ap WHERE a.SID = s.SID AND a.SERIAL# = s.SERIAL# AND a.APPLY_NAME = ap.APPLY_NAME order by a.apply_name, a.server_id; Col apply_name Heading 'Apply Name' FORMAT A30 Col server_id Heading 'Apply Server Number' FORMAT 99999999 Col sqltext Heading 'Current SQL' FORMAT A64 prompt Using V$SQL select a.inst_id, a.apply_name, a.server_id, q.sql_id,q.sql_fulltext sqltext from gv$streams_apply_server a, gv$sql q, gv$session s where a.sid = s.sid and s.sql_hash_value = q.hash_value and s.sql_address = q.address and s.sql_id = q.sql_id order by a.apply_name, a.server_id; prompt Col apply_name Heading 'Apply Name' FORMAT A30 Col server_id Heading 'Apply Server Number' FORMAT 99999999 Col event Heading 'Wait Event' FORMAT A64 Col secs Heading 'Seconds Waiting' FORMAT 9999999999999999 prompt Top Wait event per server select a.inst_id, a.apply_name, a.server_id, w.event, w.seconds_in_wait secs from gv$streams_apply_server a, gv$session_wait w where a.sid = w.sid order by a.apply_name, a.server_id; prompt Col apply_name Heading 'Apply Name' FORMAT A30 Col server_id Heading 'Apply Server Number' FORMAT 99999999 Col event Heading 'Wait Event' FORMAT 99999999 Col total_waits Heading 'Total Waits' FORMAT 9999999999999999 Col total_timeouts Heading 'Total Timeouts' FORMAT 9999999999999999 Col time_waited Heading 'Time Waited' FORMAT 9999999999999999 Col average_wait Heading 'Average Wait' FORMAT 9999999999999999 Col max_wait Heading 'Maximum Wait' FORMAT 9999999999999999 prompt Wait events per server select a.inst_id, a.apply_name, a.server_id, e.event, e.total_waits, e.total_timeouts, e.time_waited, e.average_wait, e.max_wait from gv$streams_apply_server a, gv$session_event e where a.sid = e.sid order by a.apply_name, a.server_id,e.time_waited desc; col current_txn format a15 wrap col dependent_txn Heading 'Dependent|Transaction' format a15 wrap col dep_commitscn Heading 'Dependent|Commit SCN' prompt prompt Apply server transactions ordered by server_id prompt select a.APPLY_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, server_id SRVR,a.state, a.TOTAL_ASSIGNED ASSIGNED, a.TOTAL_MESSAGES_APPLIED msg_APPLIED, xidusn||'.'||xidslt||'.'||xidsqn CURRENT_TXN, commitscn, dep_xidusn||'.'||dep_xidslt||'.'||dep_xidsqn DEPENDENT_TXN, dep_commitscn, message_sequence, applied_message_number, APPLIED_MESSAGE_CREATE_TIME, apply_time FROM gV$STREAMS_APPLY_SERVER a, gV$SESSION s WHERE a.SID = s.SID AND a.SERIAL# = s.SERIAL# order by a.apply_name,a.server_id; prompt prompt Apply server transactions ordered by souce commitscn and dependent transaction scns. prompt select a.APPLY_NAME, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, server_id SRVR,a.state, a.TOTAL_ASSIGNED ASSIGNED, a.TOTAL_MESSAGES_APPLIED msg_APPLIED, xidusn||'.'||xidslt||'.'||xidsqn CURRENT_TXN, commitscn, dep_xidusn||'.'||dep_xidslt||'.'||dep_xidsqn DEPENDENT_TXN, dep_commitscn, message_sequence, applied_message_number, APPLIED_MESSAGE_CREATE_TIME, apply_time FROM gV$STREAMS_APPLY_SERVER a, gV$SESSION s WHERE a.SID = s.SID AND a.SERIAL# = s.SERIAL# order by a.apply_name,a.commitscn, a.dep_commitscn; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ++ APPLY PROGRESS ++ col oldest_message_number HEADING 'Oldest|Message|SCN' col apply_time HEADING 'Apply|Timestamp' select * from dba_apply_progress order by apply_name; prompt ============================================================================================ prompt prompt ++ OPEN STREAMS APPLY TRANSACTIONS ++ prompt prompt +** Count **+ select streams_name, count(*) "Open Transactions",sum(cumulative_message_count) "Total LCRs" from gv$streams_transaction where streams_type='APPLY' group by streams_name; prompt prompt +** Detail **+ select * from gv$streams_transaction where streams_type='APPLY' order by streams_name,first_message_number; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt prompt ++ Split/Merge Activity ++ prompt COLUMN ORIGINAL_CAPTURE_NAME HEADING 'Original|Capture|Process' column CLONED_CAPTURE_NAME HEADING 'Cloned|Capture|Process' column original_queue_owner Heading 'Original|Queue|Owner' column original_queue_name Heading 'Original|Queue|Name' column cloned_queue_owner Heading 'Cloned|Queue|Owner' column cloned_queue_name Heading 'Cloned|Queue|Name' column Original_capture_status HEADING 'Original|Capture|Status' column Cloned_capture_status Heading 'Cloned|Capture|Status' column ORiGINAL_STREAMS_NAME HEADING 'Original|Streams|Name' column Cloned_Streams_name Heading 'Cloned|Streams|Name' column Streams_type Heading 'Streams|Type' Column Recoverable_script_id Heading 'Recoverable|Script|ID' Column Script_status Heading 'Script|Status' column LAG Heading 'Lag' column Job_owner Heading 'Job|Owner' Column Job_name Heading 'Job|Name' Column Job_state Heading 'Job|State' Column Error_number Heading 'Error|Number' Column Error_message Heading 'Error|Message' COLUMN ACTION_TYPE HEADING 'Action|Type' COLUMN STATUS_UPDATE_TIME HEADING 'Status|Update|Time' COLUMN STATUS HEADING 'Status' COLUMN JOB_NEXT_RUN_DATE HEADING 'Next Job|Run Date' column creation_time Heading 'Creation|Time' column Action_threshold Heading 'Action|Threshold' Select * FROM DBA_STREAMS_SPLIT_MERGE ORDER BY STATUS_UPDATE_TIME DESC; prompt prompt ++ Split/Merge History (last 10 days) ++ prompt select * From dba_streams_split_merge_hist where creation_time > systimestamp-10order by creation_time desc ; prompt prompt prompt ++ Streams Topology ++ prompt exec dbms_streams_advisor_adm.ANALYZE_CURRENT_PERFORMANCE ; exec dbms_lock.sleep(5); exec dbms_streams_advisor_adm.ANALYZE_CURRENT_PERFORMANCE; exec dbms_lock.sleep(5); exec dbms_streams_advisor_adm.ANALYZE_CURRENT_PERFORMANCE; REM exec utl_spadv.show_stats column global_name format a50 column component_id format 9999999 column component_name format a25 wrap column component_db format a25 wrap column component_type format a20 wrap column fromm Heading 'FROM|Component' format 99999 column source_component_id format 9999999 column source_component_name HEADING 'Source|Component' format a25 wrap column source_component_db HEADING 'Source |Database' format a25 wrap column source_component_type HEADING 'Type' format a20 wrap column destination_component_id format 9999999 column destination_component_name HEADING 'Destination|Component' format a25 wrap column destination_component_db HEADING 'Destination|Database' format a25 wrap column destination_component_type Heading 'Type' format a20 wrap column too heading 'TO |Component' format 99999 column top_session_id HEADING 'Top|Session SID' format 999999 column top_session_serial# HEADING 'Top|Session Serial#' format 999999 prompt ++ Topology Databases ++ prompt select * from dba_streams_tp_database; prompt prompt ++ Streams Components ++ prompt select * from dba_streams_tp_component order by component_id; prompt ++ Streams Component Statistics ++ prompt select advisor_run_id , component_id, component_name, component_db, component_type, sub_component_type , statistic_time, statistic_name, statistic_value, statistic_unit , session_id, session_serial# , advisor_run_time from dba_streams_tp_component_stat order by component_id, advisor_run_id,statistic_name; prompt prompt ++ Streams Active Paths ++ prompt select path_id,position,source_component_id fromm,source_component_db,source_component_name,source_component_type, destination_component_id too,destination_component_db, destination_component_name,destination_component_type from dba_streams_tp_component_link order by path_id,position; prompt -- prompt -- prompt ++ Streams Path Highest Activity Process (Bottleneck) ++ -- prompt select * from dba_streams_tp_path_bottleneck where bottleneck_identified='YES' and advisor_run_id =(select max(advisor_run_id) from dba_streams_tp_path_bottleneck )order by path_id, advisor_run_id; prompt ++ Streams Path Statistics ++ prompt col latency format a15 col transaction_rate format a40 col message_rate format a40 select path_id,advisor_run_id,advisor_run_time ,max(case when statistic_name='LATENCY' then statistic_value||' '||statistic_unit end) latency ,max(case when statistic_name='TRANSACTION RATE' then statistic_value||' '||statistic_unit end) transaction_rate ,max(case when statistic_name='MESSAGE RATE' then statistic_value||' '||statistic_unit end) message_rate from dba_streams_tp_path_stat group by path_id,advisor_run_id,advisor_run_time order by 1,2,3; prompt prompt ++ Streams Message Tracking ++ prompt col message_number Heading 'Message|Number' col tracking_label Heading 'Tracking|Label' col Component_name Heading 'Component|Name' col Component_type Heading 'Component|Type' col action Heading 'Action' col action_details Heading 'Action|Details' col Message_creation_time Heading 'Message Creation|Time' col tracking_id Heading 'Tracking|ID' col source_database_name Heading 'Source|Database' col object_owner Heading 'Owner|Name' col object_name Heading 'Object|Name' col command_type Heading 'Command|Type' col message_position Heading 'Message|Position' select * from gv$streams_message_tracking order by tracking_label,timestamp; prompt prompt prompt ++ STATISTICS on RULES and RULE SETS ++ prompt ++ prompt ++ RULE SET STATISTICS ++ prompt col name HEADING 'Name' select * from gv$rule_set; prompt prompt ++ RULE STATISTICS ++ prompt select * from gv$rule; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt ================================================================================ prompt ++ STREAMS Process Wait Analysis ++ prompt set numf 9999999999999 set pages 9999 set verify OFF COL BUSY FORMAT A4 COL PERCENTAGE FORMAT 999D9 COL event wrapped -- This variable controls how many minutes in the past to analyze DEFINE minutes_to_analyze = 30 prompt Analysis of last &minutes_to_analyze minutes of Streams processes prompt PROMPT Note: When computing the busiest component, be sure to subtract the percentage where BUSY = 'NO' PROMPT Note: 'no rows selected' means that the process was performing no busy work, or that no such process exists on the system. PROMPT Note: A null Wait Event implies running - either on the cpu or waiting for cpu prompt prompt ++ LOGMINER READER PROCESSES ++ COL LOGMINER_READER_NAME FORMAT A30 WRAP BREAK ON LOGMINER_READER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON LOGMINER_READER_NAME; SELECT c.capture_name || ' - reader' as logminer_reader_name, ash_capture.event_count, ash_total.total_count, ash_capture.event_count*100/ash_total.total_count percentage, 'YES' busy, ash_capture.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash_capture, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$logmnr_process lp, v$streams_capture c WHERE lp.SID = ash_capture.SESSION_ID AND lp.serial# = ash_capture.SESSION_SERIAL# AND lp.role = 'reader' and lp.session_id = c.logminer_id ORDER BY logminer_reader_name, percentage; prompt prompt ++ LOGMINER PREPARER PROCESSES ++ COL LOGMINER_PREPARER_NAME FORMAT A30 WRAP BREAK ON LOGMINER_PREPARER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON LOGMINER_PREPARER_NAME; SELECT c.capture_name || ' - preparer' || lp.spid as logminer_preparer_name, ash_capture.event_count, ash_total.total_count, ash_capture.event_count*100/ash_total.total_count percentage, 'YES' busy, ash_capture.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash_capture, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$logmnr_process lp, v$streams_capture c WHERE lp.SID = ash_capture.SESSION_ID AND lp.serial# = ash_capture.SESSION_SERIAL# AND lp.role = 'preparer' and lp.session_id = c.logminer_id ORDER BY logminer_preparer_name, percentage; prompt prompt ++ LOGMINER BUILDER PROCESSES ++ COL LOGMINER_BUILDER_NAME FORMAT A30 WRAP BREAK ON LOGMINER_BUILDER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON LOGMINER_BUILDER_NAME; SELECT c.capture_name || ' - builder' as logminer_builder_name, ash_capture.event_count, ash_total.total_count, ash_capture.event_count*100/ash_total.total_count percentage, 'YES' busy, ash_capture.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash_capture, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$logmnr_process lp, v$streams_capture c WHERE lp.SID = ash_capture.SESSION_ID AND lp.serial# = ash_capture.SESSION_SERIAL# AND lp.role = 'builder' and lp.session_id = c.logminer_id ORDER BY logminer_builder_name, percentage; prompt prompt ++ CAPTURE PROCESSES ++ COL CAPTURE_NAME FORMAT A30 WRAP BREAK ON CAPTURE_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON CAPTURE_NAME; SELECT c.capture_name, ash_capture.event_count, ash_total.total_count, ash_capture.event_count*100/ash_total.total_count percentage, DECODE(ash_capture.event, 'Streams capture: waiting for subscribers to catch up', 'NO', 'Streams capture: resolve low memory condition', 'NO', 'Streams capture: waiting for archive log', 'NO', 'YES') busy, ash_capture.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash_capture, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$streams_capture c WHERE c.SID = ash_capture.SESSION_ID and c.serial# = ash_capture.SESSION_SERIAL# ORDER BY capture_name, percentage; prompt prompt ++ PROPAGATION SENDER PROCESSES ++ COL PROPAGATION_NAME FORMAT A30 WRAP BREAK ON PROPAGATION_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON PROPAGATION_NAME; SELECT ('"'||vps.queue_schema||'"."'||vps.queue_name|| '"=>'||vps.dblink) as propagation_name, ash.event_count, ash_total.total_count, ash.event_count*100/ash_total.total_count percentage, DECODE(ash.event, 'SQL*Net more data to dblink', 'NO', 'SQL*Net message from dblink', 'NO', 'YES') busy, ash.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$propagation_sender vps, x$kwqps xps WHERE xps.kwqpssid = ash.SESSION_ID and xps.kwqpsser = ash.SESSION_SERIAL# AND xps.kwqpsqid = vps.queue_id and vps.dblink = xps.KWQPSDBN ORDER BY propagation_name, percentage; prompt prompt ++ PROPAGATION RECEIVER PROCESSES ++ COL PROPAGATION_RECEIVER_NAME FORMAT A30 WRAP BREAK ON PROPAGATION_RECEIVER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON PROPAGATION_RECEIVER_NAME; SELECT ('"'||vpr.src_queue_schema||'"."'||vpr.src_queue_name|| '@' || vpr.src_dbname|| '"=>'||global_name) as propagation_receiver_name, ash.event_count, ash_total.total_count, ash.event_count*100/ash_total.total_count percentage, DECODE(ash.event, 'Streams AQ: enqueue blocked on low memory', 'NO', 'Streams AQ: enqueue blocked due to flow control', 'NO', 'YES') busy, ash.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$propagation_receiver vpr, x$kwqpd xpd, global_name WHERE xpd.kwqpdsid = ash.SESSION_ID and xpd.kwqpdser = ash.SESSION_SERIAL# AND xpd.kwqpdsqn = vpr.src_queue_name AND xpd.kwqpdsqs = vpr.src_queue_schema and xpd.kwqpddbn = vpr.src_dbname ORDER BY propagation_receiver_name, percentage; prompt prompt ++ APPLY READER PROCESSES ++ COL APPLY_READER_NAME FORMAT A30 WRAP BREAK ON APPLY_READER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON APPLY_READER_NAME; SELECT a.apply_name as apply_reader_name, ash.event_count, ash_total.total_count, ash.event_count*100/ash_total.total_count percentage, DECODE(ash.event, 'rdbms ipc message', 'NO', 'YES') busy, ash.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$streams_apply_reader a WHERE a.sid = ash.SESSION_ID and a.serial# = ash.SESSION_SERIAL# ORDER BY apply_reader_name, percentage; prompt prompt ++ APPLY COORDINATOR PROCESSES ++ COL APPLY_COORDINATOR_NAME FORMAT A30 WRAP BREAK ON APPLY_COORDINATOR_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON APPLY_COORDINATOR_NAME; SELECT a.apply_name as apply_coordinator_name, ash.event_count, ash_total.total_count, ash.event_count*100/ash_total.total_count percentage, 'YES' busy, ash.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$streams_apply_coordinator a WHERE a.sid = ash.SESSION_ID and a.serial# = ash.SESSION_SERIAL# ORDER BY apply_coordinator_name, percentage; prompt prompt ++ APPLY SERVER PROCESSES ++ COL APPLY_SERVER_NAME FORMAT A30 WRAP BREAK ON APPLY_SERVER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON APPLY_SERVER_NAME; SELECT a.apply_name || ' - ' || a.server_id as apply_server_name, ash.event_count, ash_total.total_count, ash.event_count*100/ash_total.total_count percentage, 'YES' busy, ash.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$streams_apply_server a WHERE a.sid = ash.SESSION_ID and a.serial# = ash.SESSION_SERIAL# ORDER BY apply_server_name, percentage; prompt prompt ++ XStream OUTBOUND SERVER PROCESSES ++ COL SERVER_NAME FORMAT A30 WRAP BREAK ON SERVER_NAME; COMPUTE SUM LABEL 'TOTAL' OF PERCENTAGE ON SERVER_NAME; SELECT a.server_name , ash.event_count, ash_total.total_count, ash.event_count*100/ash_total.total_count percentage, 'YES' busy, ash.event FROM (SELECT SESSION_ID, SESSION_SERIAL#, EVENT, COUNT(sample_time) AS EVENT_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60 GROUP BY session_id, session_serial#, event) ash, (SELECT COUNT(DISTINCT sample_time) AS TOTAL_COUNT FROM v$active_session_history WHERE sample_time > sysdate - &minutes_to_analyze/24/60) ash_total, v$xstream_outbound_server a WHERE a.sid = ash.SESSION_ID and a.serial# = ash.SESSION_SERIAL# ORDER BY server_name, percentage; prompt prompt Configuration: Database Queue Capture Propagation Apply XStream prompt Analysis: History Rules Notifications Configuration Performance Wait Analysis Topology prompt Statistics: Streams Statistics Queue Capture Propagation Apply Apply_Errors XStream Outbound XStream Inbound prompt set heading off select 'STREAMS Health Check (&hcversion) for '||global_name||' on Instance='||instance_name||' generated: '||sysdate o from global_name, v$instance; set heading on set timing off set markup html off clear col clear break spool prompt Turning Spool OFF!!! spool off