-- -----------------------------------------------------------
-- Replication Group Setup
-- -----------------------------------------------------------

SELECT *
FROM dba_repsites@VRCMS.develop.central
ORDER BY dblink
, gname;

SELECT * FROM all_repgroup@dblink1;
SELECT * FROM all_repgroup@dblink2;
SELECT * FROM all_repgroup@dblink3;

select * from all_repobject@dblink1 where gname = 'REPGROUP_B';
select * from all_repobject@dblink2 where gname = 'REPGROUP_B';
select * from all_repobject@dblink3 where gname = 'REPGROUP_B';

select SNAME,',', ONAME from sys.all_repobject where generation_status like 'NEEDSGEN';

-- -----------------------------------------------------------
-- Database Links
-- -----------------------------------------------------------

SELECT * from all_db_links@dblink1;
SELECT * from all_db_links@dblink2;
SELECT * from all_db_links@dblink3;

-- -----------------------------------------------------------
-- Suspend Replication
-- -----------------------------------------------------------

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'REPGROUP_A');
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'REPGROUP_B');
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY ( gname => 'REPGROUP_C');
END;

BEGIN
DBMS_REPUTIL.REPLICATION_OFF;
END;

-- -----------------------------------------------------------
-- Resume Replication
-- -----------------------------------------------------------

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'REPGROUP_A');
DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'REPGROUP_B');
DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'REPGROUP_C');
END;

-- -----------------------------------------------------------
-- Replication Group Removal
-- -----------------------------------------------------------

BEGIN
DBMS_REPCAT.DROP_MASTER_REPGROUP (
gname => 'REPGROUP_A',
drop_contents => false,
all_sites => false);
END;

BEGIN
DBMS_REPCAT.DROP_MASTER_REPGROUP (
gname => 'REPGROUP_B',
drop_contents => false,
all_sites => false);
END;

BEGIN
DBMS_REPCAT.DROP_MASTER_REPGROUP (
gname => 'REPGROUP_C',
drop_contents => false,
all_sites => false);
END;

-- -----------------------------------------------------------
-- Replication Group Creation
-- -----------------------------------------------------------

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'REPGROUP_A',
master => 'dblink2',
use_existing_objects => true,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'REPGROUP_B',
master => 'dblink2',
use_existing_objects => true,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;

BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'REPGROUP_C',
master => 'dblink2',
use_existing_objects => true,
copy_rows => TRUE,
propagation_mode => 'ASYNCHRONOUS');
END;

-- -----------------------------------------------------------
-- RepCat Progress
-- -----------------------------------------------------------

SELECT gname, request, status, count(*) FROM dba_repcatlog@dblink1 GROUP BY gname, request, status ORDER BY gname, request;
SELECT gname, request, status, count(*) FROM dba_repcatlog@dblink2 GROUP BY gname, request, status ORDER BY gname, request;
SELECT gname, request, status, count(*) FROM dba_repcatlog@dblink3 GROUP BY gname, request, status ORDER BY gname, request;

SELECT * FROM dba_repcatlog@dblink1 WHERE status = 'ERROR';
SELECT * FROM dba_repcatlog@dblink2 WHERE status = 'ERROR';
SELECT * FROM dba_repcatlog@dblink3 WHERE status = 'ERROR';

-- -----------------------------------------------------------
-- RepCat Maintenance
-- -----------------------------------------------------------

BEGIN
DBMS_REPCAT.PURGE_MASTER_LOG (
id => 70952,
source => null,
gname => 'REPGROUP_A');
END;

BEGIN
DBMS_REPCAT.PURGE_MASTER_LOG (
id => null,
source => null,
gname => 'REPGROUP_B');
END;

-- -----------------------------------------------------------
-- Transaction Replication Setup
-- -----------------------------------------------------------

SELECT * FROM user_jobs@dblink1;
SELECT * FROM user_jobs@dblink2;
SELECT * FROM user_jobs@dblink3;

SELECT * FROM dba_jobs_running@dblink1;
SELECT * FROM dba_jobs_running@dblink2;
SELECT * FROM dba_jobs_running@dblink3;

BEGIN
DBMS_JOB.RUN(766);
END;

BEGIN
DBMS_JOB.REMOVE(766);
END;

-- -----------------------------------------------------------
-- Transaction Replication Status
-- -----------------------------------------------------------

SELECT DISTINCT(dblink), COUNT(deferred_tran_id) FROM deftrandest@dblink1 GROUP BY dblink;
SELECT DISTINCT(dblink), COUNT(deferred_tran_id) FROM deftrandest@dblink2 GROUP BY dblink;
SELECT DISTINCT(dblink), COUNT(deferred_tran_id) FROM deftrandest@dblink3 GROUP BY dblink;

SELECT * FROM deferror@dblink1 order by start_time DESC;
SELECT * FROM deferror@dblink2 order by start_time DESC;
SELECT * FROM deferror@dblink3 order by start_time DESC;

-- -----------------------------------------------------------
-- Transaction Replication Maintenance
-- -----------------------------------------------------------

DECLARE
temp INTEGER;
BEGIN
temp := DBMS_DEFER_SYS.PURGE (purge_method => DBMS_DEFER_SYS.PURGE_METHOD_QUICK);
END;

BEGIN
dbms_defer_sys.delete_error(NULL,NULL);
END;

DECLARE
temp INTEGER;
BEGIN
temp := DBMS_DEFER_SYS.PUSH (
destination => 'dblink1',
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;

DECLARE
temp INTEGER;
BEGIN
temp := DBMS_DEFER_SYS.PUSH (
destination => 'dblink2',
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;