Replication Tips

/* ******************************************************************************/
/* Now if you insert some data in 1 place you should see it (after the push)  in the other site.                    */
/* Any failure to propagate the data will be written in the directory bdump                                               */
/* ******************************************************************************/

rem Final Notes and Actions
rem At this time the do_deferred_repcat_admin job on each site should be processing the
rem requests in the sys.dba_repcatlog view.
rem Connect as REPADMIN on each side (REP1 and REP2) and run the following queries:
rem select count(*) from sys.dba_repcatlog;
rem select gname, status from sys.dba_repcat;
rem When the first query returns 0 rows on BOTH sites and the second query returns a
rem STATUS=NORMAL, you can proceed with your DML statements on your replicated tables
rem (INSERT, UPDATE, DELETE) and watch the data get propagated between sites.
rem You can also manually execute do_deferred_repcat_admin on both sites to speed up the
rem processing of requests from the sys.dba_repcatlog view.
rem As noted above run the queries on the sys.dba_repcatlog and sys.dba_repcat views to
rem ensure you are ready to replicate data.

rem -----------------------------------------------------------------------
rem Filename:   repmon.sql
rem Purpose:    Monitor replication status, sites and groups
rem Date:       03-Oct-2000
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------

connect repadmin
set pages 50000

col sname     format a20 head "SchemaName"
col masterdef format a10 head "MasterDef?"
col oname     format a20 head "ObjectName"
col gname     format a20 head "GroupName"
col object    format a35 trunc
col dblink    format a35 head "DBLink"
col message   format a25
col broken    format a6  head "Broken?"

prompt Replication schemas/ sites
select sname, masterdef, dblink
from   sys.dba_repschema;

prompt Replication Catalog (after a while you should see no entries):
prompt Entries in dba_repcatlog will have a status of AWAIT CALLBACK at the masterdef site if they are waiting for a response back from a master site.
select id, substr(source,1,10) source, status, substr(userid,1,8) userid, timestamp, substr(master,1,10) master,
          substr(REQUEST,1,20) request, substr(sname,1,6) schema, substr(oname,1,6) object_name, message
order by id;

prompt Check Init.ora Parameters
SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME in ('job_queue_processes','job_queue_interval');

prompt Entries in the job queue
select job, last_date, last_sec, next_date, next_sec, broken, failures, what
from   sys.dba_jobs
where  schema_user = 'REPADMIN';

-- prompt Run Jobs manually
-- EXECUTE DBMS_JOB.RUN(<job#_from_step1);

prompt Group Replication Status:
prompt if is not NORMAL use : execute dbms_repcat.resume_master_activity('CO_OWNER_REPGRP');
select sname, gname, master, status from sys.dba_repcat;

-- Returns all conflict resolution methods
-- select * from all_repconflict;

-- Returns all resolution methods in use
-- select * from all_represolution;

prompt Objects registered for replication
select gname, type||' '||sname||'.'||oname object, status, generation_status
from   sys.dba_repobject;

rem -----------------------------------------------------------------------
rem Filename:   repdel.sql
rem Purpose:    Remove replication support from database
rem Date:         03-Oct-2000
rem Author:     Diego Pafumi
rem -----------------------------------------------------------------------

spool repdel
connect repadmin

REM Stop replication
execute dbms_repcat.suspend_master_activity(gname='CO_OWNER_REPGRP');

REM Delete replication groups
-- execute dbms_repcat.drop_master_repobject('CO_OWNER_REPGRP', 'ONLINE_CONTENT', 'TABLE');
execute dbms_repcat.drop_master_repgroup('CO_OWNER_REPGRP');
execute dbms_repcat.remove_master_databases('CO_OWNER_REPGRP', 'TICCOP3A');

REM Remove private databse links to other master databases
drop database link TICCOP3B;

connect system

REM Remove the REPADMIN user
execute dbms_defer_sys.unregister_propagator(username='REPADMIN');
execute dbms_repcat_admin.revoke_admin_any_schema(username='REPADMIN');
drop user repadmin cascade;

REM Drop public database links to other master databases
drop public database link TICCOP3B;

spool off