Oracle® Advanced Replication Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

I was asked questions relating Replication in 10g.

As usual, I thought I should answer the question by using examples. This is following steps using the examples in the following links. I have chosen to use 2 Master sites instead of the 3 in the document.

Create Replication Site
Create a Master Group
Monitoring a Replication Environment


I used 10g Release 10.1 document because it was the first one I picked, but you can use the 10g Release 10.2 document.

Note: I have not hidden my passwords because it is my stand-alone test on a Laptop. I do not recommend exposing your passwords on the system.

This test as mentioned in the documentation assumes that the HR example schema has been created on both databases using the same script/structure. You will find the scripts under $ORACLE_HOME/demo/schema.

This shows the results of the examples and should be read with the document in the above links.

-- Setup Master Site Database TESTDB – This Database is on a different ORACLE_HOME on same Test Laptop as EMREP1. Oracle 10g Realease 10.2.0.2

Step 1 Connect as SYSTEM at a master site TESTDB

SQL> connect system/oracl3
Connected.

SQL>

Step 2 Create the replication administrator at TESTDB

SQL> CREATE USER repadmin IDENTIFIED BY repadmin;

User created.

SQL>

Step 3 Grant privileges to the replication administrator at TESTDB

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> GRANT COMMENT ANY TABLE TO repadmin;

Grant succeeded.

SQL> GRANT LOCK ANY TABLE TO repadmin;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO repadmin;

Grant succeeded.

SQL>

Step 4 Register the propagator at TESTDB

SQL> BEGIN
2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Step 5 Register the receiver at TESTDB

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'repadmin',
4 privilege_type => 'receiver',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 6 Schedule purge at master site TESTDB

SQL> CONNECT repadmin/repadmin
Connected.
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
3 next_date => SYSDATE,
4 interval => 'SYSDATE + 1/24',
5 delay_seconds => 0);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 7 Create proxy master site users at TESTDB

SQL> connect system/oracl3
Connected.
SQL> CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

User created.

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'proxy_mviewadmin',
4 privilege_type => 'proxy_snapadmin',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL> GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Grant succeeded.

SQL> CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

User created.

SQL> GRANT CREATE SESSION TO proxy_refresher;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO proxy_refresher;

Grant succeeded.

SQL>

-- Setup Master Site Database EMREP1 – This Database is on a different ORACLE_HOME on same Test Laptop as TESTDB. Oracle 10g Realease 10.1.0.4

Step 1 Connect as SYSTEM at a master site EMREP1

SQL> conn system/oracl3
Connected.

SQL>

Step 2 Create the replication administrator at EMREP1

SQL> create user REPADMIN identified by REPADMIN;

User created.

SQL>

Step 3 Grant privileges to the replication administrator at EMREP1

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> GRANT COMMENT ANY TABLE TO repadmin;

Grant succeeded.

SQL> GRANT LOCK ANY TABLE TO repadmin;

Grant succeeded.

SQL>
SQL> GRANT SELECT ANY DICTIONARY TO repadmin;

Grant succeeded.

SQL>

Step 4 Register the propagator at EMREP1

SQL> BEGIN
2 DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
3 username => 'repadmin');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Step 5 Register the receiver at EMREP1

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'repadmin',
4 privilege_type => 'receiver',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 6 Schedule purge at master site EMREP1

SQL> CONNECT repadmin/repadmin
Connected.
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PURGE (
3 next_date => SYSDATE,
4 interval => 'SYSDATE + 1/24',
5 delay_seconds => 0);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>

Step 7 Create proxy master site users at EMREP1

SQL> conn system/oracl3
Connected.
SQL> CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

User created.

SQL> BEGIN
2 DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
3 username => 'proxy_mviewadmin',
4 privilege_type => 'proxy_snapadmin',
5 list_of_gnames => NULL);
6 END;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Grant succeeded.

SQL> CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

User created.

SQL>
SQL> GRANT CREATE SESSION TO proxy_refresher;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO proxy_refresher;

Grant succeeded.

SQL>


-- Create Scheduled Links Between Master Sites at TESTDB

Step 1 Create database links between master sites

SQL> CONNECT SYSTEM/oracl3
Connected.
SQL> CREATE PUBLIC DATABASE LINK emrep1 USING 'emrep1';

Database link created.

SQL> CONNECT repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK emrep1 CONNECT TO repadmin IDENTIFIED BY repadmin;

Database link created.

SQL>

Step 2 Define a schedule for each database link to create scheduled links

SQL> CONNECT repadmin/repadmin
Connected.
SQL>
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'emrep1',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>

-- Create Scheduled Links Between Master Sites at EMREP1

Step 1 Create database links between master sites

SQL> CONNECT SYSTEM/oracl3
Connected.
SQL> CREATE PUBLIC DATABASE LINK testdb USING 'testdb';

Database link created.

SQL>
SQL> CONNECT repadmin/repadmin
Connected.
SQL> CREATE DATABASE LINK testdb CONNECT TO repadmin IDENTIFIED BY repadmin;

Database link created.

SQL>

Step 2 Define a schedule for each database link to create scheduled links

SQL> CONNECT repadmin/repadmin
Connected.
SQL>
SQL> BEGIN
2 DBMS_DEFER_SYS.SCHEDULE_PUSH (
3 destination => 'testdb',
4 interval => 'SYSDATE + (1/144)',
5 next_date => SYSDATE,
6 parallelism => 1,
7 execution_seconds => 1500,
8 delay_seconds => 1200);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>



-- Create Master Group in TESTDB

Step 1 Create the schema at master sites at TESTDB

SQL> SET ECHO ON
SQL>
SQL> SPOOL create_mg.out
SQL>
SQL> CONNECT repadmin/repadmin
Connected.
SQL> PAUSE Press to continue when the schema exists at all master sites.
Press to continue when the schema exists at all master sites.
SQL>

Step 2 Create the master group at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPGROUP (
3 gname => 'hr_repg');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

Step 3 Add objects to master group at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'countries',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> BEGIN
SQL> DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
SQL> gname => 'hr_repg',
SQL> type => 'TABLE',
SQL> oname => 'countries',
SQL> sname => 'hr',
SQL> use_existing_object => TRUE,
SQL> copy_rows => FALSE);
SQL> END;
SQL> /
SQL> */
SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'departments',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'employees',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'jobs',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'job_history',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'locations',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'TABLE',
5 oname => 'regions',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'dept_location_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'emp_department_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'emp_job_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'emp_manager_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'jhist_department_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'jhist_employee_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'jhist_job_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
3 gname => 'hr_repg',
4 type => 'INDEX',
5 oname => 'loc_country_ix',
6 sname => 'hr',
7 use_existing_object => TRUE,
8 copy_rows => FALSE);
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>

Step 4 Add additional master site for EMREP1 at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.ADD_MASTER_DATABASE (
3 gname => 'hr_repg',
4 master => 'emrep1',
5 use_existing_objects => TRUE,
6 copy_rows => FALSE,
7 propagation_mode => 'ASYNCHRONOUS');
8 END;
9 /

PL/SQL procedure successfully completed.

SQL>

Step 5 If conflicts are possible, then configure conflict resolution methods

SQL>

SQL> PAUSE Press to continue after configuring conflict resolution methods
Press to continue after configuring conflict resolution methods
or if no conflict resolution methods are required.

SQL>

Step 6 Generate replication support at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'countries',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'departments',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'employees',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'jobs',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'job_history',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'locations',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
2 DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
3 sname => 'hr',
4 oname => 'regions',
5 type => 'TABLE',
6 min_communication => TRUE);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';

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

SQL>

Step 7 Start replication at TESTDB

SQL> BEGIN
2 DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
3 gname => 'hr_repg');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>

-- Monitor Replication Environment

SQL> SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HR_REPG';

DBLINK
--------------------------------------------------------------------------------
EMREP1
TESTDB

SQL>
SQL> COLUMN GLOBAL_NAME HEADING 'Database' FORMAT A25
SQL> COLUMN ADMIN_REQUESTS HEADING 'AdminReqests' FORMAT 9999
SQL> COLUMN STATUS HEADING 'AdminErrors' FORMAT 9999
SQL> COLUMN TRAN HEADING 'DefTransPairs' FORMAT 9999
SQL> COLUMN ERRORS HEADING 'DefTransErrors' FORMAT 9999
SQL> COLUMN COMPLETE HEADING 'PropagatedTrans' FORMAT 9999
SQL> SELECT G.GLOBAL_NAME, D.ADMIN_REQUESTS, E.STATUS, DT.TRAN, DE.ERRORS, C.COMPLETE
2 FROM (SELECT GLOBAL_NAME FROM GLOBAL_NAME) G,
3 (SELECT COUNT(ID) ADMIN_REQUESTS FROM DBA_REPCATLOG) D,
4 (SELECT COUNT(STATUS) STATUS FROM DBA_REPCATLOG WHERE STATUS = 'ERROR') E,
5 (SELECT COUNT(*) TRAN FROM DEFTRANDEST) DT,
6 (SELECT COUNT(*) ERRORS FROM DEFERROR) DE,
7 (SELECT COUNT(A.DEFERRED_TRAN_ID) COMPLETE FROM DEFTRAN A
8 WHERE A.DEFERRED_TRAN_ID NOT IN (
9 SELECT B.DEFERRED_TRAN_ID FROM DEFTRANDEST B)) C;

Def Def
Admin Admin Trans Trans Propagated
Database Reqests Errors Pairs Errors Trans
------------------------- ------- ------ ----- ------ ----------
TESTDB 0 0 0 0 0

SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A20
SQL> COLUMN DBLINK HEADING 'Sites' FORMAT A25
SQL> COLUMN MASTERDEF HEADING 'MasterDefinitionSite?' FORMAT A10
SQL> SELECT GNAME, DBLINK, MASTERDEF
2 FROM DBA_REPSITES
3 WHERE MASTER = 'Y'
4 AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')
5 ORDER BY GNAME;

Master
Definition
Master Group Sites Site?
-------------------- ------------------------- ----------
HR_REPG EMREP1 N
HR_REPG TESTDB Y

SQL> COLUMN GNAME HEADING 'Master Group' FORMAT A15
SQL> COLUMN deftran HEADING 'Number ofDeferredTransactionPairs' FORMAT 9999
SQL> COLUMN deftranerror HEADING 'Number ofDeferredTransactionErrors' FORMAT 9999
SQL> COLUMN adminreq HEADING 'Number ofAdministrativeRequests' FORMAT 9999
SQL> COLUMN adminreqerror HEADING 'Number ofAdministrativeRequestErrors'
SQL> COLUMN adminreqerror FORMAT 9999
SQL>
SQL> SELECT G.GNAME,
2 NVL(T.CNT1, 0) deftran,
3 NVL(IE.CNT2, 0) deftranerror,
4 NVL(A.CNT3, 0) adminreq,
5 NVL(B.CNT4, 0) adminreqerror
6 FROM
7 (SELECT DISTINCT GNAME FROM DBA_REPGROUP WHERE MASTER='Y') G,
8 (SELECT DISTINCT RO.GNAME, COUNT(DISTINCT D.DEFERRED_TRAN_ID) CNT1
9 FROM DBA_REPOBJECT RO, DEFCALL D, DEFTRANDEST TD
10 WHERE RO.SNAME = D.SCHEMANAME
11 AND RO.ONAME = D.PACKAGENAME
12 AND RO.TYPE IN ('TABLE', 'PACKAGE', 'MATERIALIZED VIEW')
13 AND TD.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
14 GROUP BY RO.GNAME ) T,
15 (SELECT DISTINCT RO.GNAME, COUNT(DISTINCT E.DEFERRED_TRAN_ID) CNT2
16 FROM DBA_REPOBJECT RO, DEFCALL D, DEFERROR E
17 WHERE RO.SNAME = D.SCHEMANAME
18 AND RO.ONAME = D.PACKAGENAME
19 AND RO.TYPE IN ('TABLE', 'PACKAGE', 'MATERIALIZED VIEW')
20 AND E.DEFERRED_TRAN_ID = D.DEFERRED_TRAN_ID
21 AND E.CALLNO = D.CALLNO
22 GROUP BY RO.GNAME ) IE,
23 (SELECT GNAME, COUNT(*) CNT3 FROM DBA_REPCATLOG GROUP BY GNAME) A,
24 (SELECT GNAME, COUNT(*) CNT4 FROM DBA_REPCATLOG
25 WHERE STATUS = 'ERROR'
26 GROUP BY GNAME) B WHERE G.GNAME = IE.GNAME (+)
27 AND G.GNAME = T.GNAME (+)
28 AND G.GNAME = A.GNAME (+)
29 AND G.GNAME = B.GNAME (+) ORDER BY G.GNAME;

Number of Number of Number of
Deferred Deferred Number of Administrative
Transaction Transaction Administrative Request
Master Group Pairs Errors Requests Errors
--------------- ----------- ----------- -------------- --------------
HR_REPG 0 0 0 0


SQL> COLUMN repgroup HEADING 'Number ofReplicationGroups' FORMAT 9999
SQL> COLUMN mvgroup HEADING 'Number ofRegisteredMV Groups' FORMAT 9999
SQL> COLUMN mv HEADING 'Number ofRegistered MVs' FORMAT 9999
SQL> COLUMN mvlog HEADING 'Number ofMV Logs' FORMAT 9999
SQL> COLUMN template HEADING 'Number ofTemplates' FORMAT 9999
SQL> SELECT A.REPGROUP repgroup,
2 B.MVGROUP mvgroup,
3 C.MV mv,
4 D.MVLOG mvlog,
5 E.TEMPLATE template
6 FROM (SELECT COUNT(G.GNAME) REPGROUP
7 FROM DBA_REPGROUP G, DBA_REPSITES S
8 WHERE G.MASTER = 'Y'
9 AND S.MASTER = 'Y'
10 AND G.GNAME = S.GNAME
11 AND S.MY_DBLINK = 'Y') A,
12 (SELECT COUNT(*) MVGROUP
13 FROM DBA_REGISTERED_MVIEW_GROUPS) B,
14 (SELECT COUNT(*) MV
15 FROM DBA_REGISTERED_MVIEWS) C,
16 (SELECT COUNT(*) MVLOG
17 FROM (SELECT 1 FROM DBA_MVIEW_LOGS
18 GROUP BY LOG_OWNER, LOG_TABLE)) D,
19 (SELECT COUNT(*) TEMPLATE FROM DBA_REPCAT_REFRESH_TEMPLATES) E;

Number of Number of
Replication Registered Number of Number of Number of
Groups MV Groups Registered MVs MV Logs Templates
----------- ---------- -------------- --------- ---------
1 0 2 0 0

SQL> COLUMN LOG_TABLE HEADING 'Log Table' FORMAT A20
SQL> COLUMN LOG_OWNER HEADING 'LogOwner' FORMAT A5
SQL> COLUMN MASTER HEADING 'Master' FORMAT A15
SQL> COLUMN ROWIDS HEADING 'RowID?' FORMAT A3
SQL> COLUMN PRIMARY_KEY HEADING 'PrimaryKey?' FORMAT A7
SQL> COLUMN OBJECT_ID HEADING 'ObjectID?' FORMAT A6
SQL> COLUMN FILTER_COLUMNS HEADING 'FilterColumns?' FORMAT A8
SQL>
SQL> SELECT DISTINCT LOG_TABLE,
2 LOG_OWNER,
3 MASTER,
4 ROWIDS,
5 PRIMARY_KEY,
6 OBJECT_ID,
7 FILTER_COLUMNS
8 FROM DBA_MVIEW_LOGS
9 ORDER BY 1;

no rows selected
SQL>

Hints:

To test that the replication setup works, try DML (Insert, Delete, Update) on any of the tables added as replication objects.

When using the HR example schema. After an update on Job table, you might get ORA-00001 UNIQUE CONSTRAINT VIOLATED error on the Job History table during replication, thereby causing the replication to fail. This is because of a trigger on Job table is trying to make a change that a replication is doing on Job history table in EMREP1 (in my example). A quick fix is to disable the trigger in EMREP1 (not in TESTDB in my example).