Search This Blog

2008-08-21

Oracle Database Capture Data (CDC) Useful Views

This is another mechanism used for data replication besides using Oracle Replication. Following are useful view


select sid, capture_name from v$streams_capture;

       SID CAPTURE_NAME
---------- ------------------------------
       539 CDC$C_GESBIO_CHANGE_SET

select sid, apply_name, total_assigned from v$streams_apply_server;

       SID APPLY_NAME                     TOTAL_ASSIGNED
---------- ------------------------------ --------------
       463 CDC$A_GESBIO_CHANGE_SET                 12351
       477 CDC$A_GESBIO_CHANGE_SET                  2765
       484 CDC$A_GESBIO_CHANGE_SET                  4745
       527 CDC$A_GESBIO_CHANGE_SET                  7592

select count(*) from dba_apply_error;

  COUNT(*)
----------
         0


Package:
Start CDC
dbms_cdc_puplish.alter_change_set (change_set_name => 'GESBIO_CHANGE_SET',
ENABLE_CAPTURE => 'Y',
STOP_ON_DDL => 'N');

Stop CDC
dbms_cdc_puplish.alter_change_set (change_set_name => 'GESBIO_CHANGE_SET',
ENABLE_CAPTURE => 'N');

Current activity
set lines 120
col state for a40
set serveroutput on size 200000

SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
         c.SID,
         c.SERIAL#,
         c.STATE,
         c.TOTAL_MESSAGES_CAPTURED,
         c.TOTAL_MESSAGES_ENQUEUED
  FROM V$STREAMS_CAPTURE c, V$SESSION s
  WHERE c.CAPTURE_NAME = 'CDC$C_GESBIO_CHANGE_SET'
  AND
  c.SID = s.SID AND
  c.SERIAL# = s.SERIAL#
/
exec print_table('select * from DBA_REGISTERED_ARCHIVED_LOG where (select REQUIRED_CHECKPOINT_SCN from dba_capture) between first_scn and next_scn')

No comments:

Blog Archive