Check the latency of an apply process

COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATION HEADING 'Message Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 99999999999999

SELECT APPLY_NAME,
     (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
     TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,
     TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
     DEQUEUED_MESSAGE_NUMBER  
  FROM V$STREAMS_APPLY_READER where apply_name='&APPLY';
Advertisements

How to set parameters for streams

Below you can see couple of examples:

exec dbms_apply_adm.set_parameter('&APPLY_NAME', 'disable_on_error', 'Y');
exec dbms_apply_adm.set_parameter('&APPLY_NAME','PARALLELISM','1')
exec dbms_capture_adm.set_parameter('&CAPTURE_NAME
','_CHECKPOINT_FREQUENCY','2000')
BEGIN
dbms_capture_adm.set_parameter(capture_name => '&CAPTURE_NAME',
                               parameter  => '_SGA_SIZE',
                               VALUE      => '300');
END;
/

LogMiner out-of-memory

First we need to get the name of the CAPTURE process and the actual value and then start tuning it:

col capture_name format a30
col parameter format a50
col value format a50
col set_by_user format a3
select capture_name,parameter, value,set_by_user from Dba_Capture_Parameters where capture_name like upper('&capture_name');
BEGIN
dbms_capture_adm.set_parameter(capture_name => '&capture_name',
                              parameter  => '_SGA_SIZE',
                              VALUE      => '300');
END;
/

ORA-24247: network access denied by access control list (ACL)

The error encountered is

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "OWNER.LDAP_NTFY", line 410
ORA-06512: at line 2

And the solution is this:

column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS; --# it looks that here we have ONLY the IP. In this case we need to add also the HOSTNAME
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_LDAPSYNC.xml','<hostname>',389,389);