Oracle 9i Multi Master Advance Replication setup, testing and troubleshooting
Objective:
This paper is related to the Multi Master Replication setup, testing and troubleshooting.
Introduction:
In this Multi Master Replication testing two machines are being used.
There will be two databases on the two different machines i.e.
a) Master1 (Master definition Site)
This site will be used for the administration of the Replication setup.
b) Master2
This will act as a second Master site in the Replication.
Replication Setup Procedure:
On Master1 Database:
1- Check the init parameters
a) SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 9.2.0.0.0
It should be same as the Oracle 9i Release which is being used for the Replication
b) SQL> show parameter shared_pool_size
NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 50331648
Add 110MB in the shared_pool_size
c) SQL> show parameter processes
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
Add 12 to the processes
d) SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
It should be TRUE
e) SQL> show parameter db_domain
NAME TYPE VALUE
———————————— ———– ——————————
db_domain string
It is the extension component of the local databases Global Name
f) SQL> show parameter open_links
NAME TYPE VALUE
———————————— ———– ——————————
open_links integer 4
open_links_per_instance integer 4
Add 2 for each master site into open_links
g) distributed_transaction * need to be clarify
h) SQL> show parameter replication
NAME TYPE VALUE
———————————— ———– ——————————
replication_dependency_tracking boolean TRUE
i) NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 10
Add 1 per additional Master
k) SQL> show parameter servers
NAME TYPE VALUE
———————————— ———– ——————————
parallel_max_servers integer 5
parallel_min_servers integer 0
set parallel_max_servers to 10
set parallel_min_servers to 2
2- Check the init parameters after modifications
a) SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 9.2.0.0.0
b) SQL> show parameter shared_pool_size
NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 167772160
c) SQL> show parameter processes
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 11
log_archive_max_processes integer 2
processes integer 170
d) SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean TRUE
e) SQL> show parameter db_domain
NAME TYPE VALUE
———————————— ———– ——————————
db_domain string world
f) SQL> show parameter open_links
NAME TYPE VALUE
———————————— ———– ——————————
open_links integer 6
open_links_per_instance integer 4
g) SQL> show parameter replication
NAME TYPE VALUE
———————————— ———– ——————————
replication_dependency_tracking boolean TRUE
h) SQL> show parameter job
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 11
i) SQL> show parameter parallel_max_servers
NAME TYPE VALUE
———————————— ———– ——————————
parallel_max_servers integer 10
SQL> show parameter parallel_min_servers
NAME TYPE VALUE
———————————— ———– ——————————
parallel_min_servers integer 2
3- Check the Tablespace Free Space
1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /
TABLESPACE_NAME FREE_SPACE
—————————— ———-
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 4.25
TOOLS 3.9375
UNDOTBS1 189.9375
USERS 24.9375
XDB .1875
System Tablespace should have 80MB free space
Undo Tablespace should have 60 MB free space
3.1) Increase the required space
a) Check the total space for each tablespace in database
SQL> l
1 select file_name,bytes/1024/1024
2* from dba_data_files
SQL> /
FILE_NAME BYTES/1024/1024
——————————————————- —————
G:\ORACLEPDB\ORADATA\MASTER1\SYSTEM01.DBF 400
G:\ORACLEPDB\ORADATA\MASTER1\UNDOTBS01.DBF 200
G:\ORACLEPDB\ORADATA\MASTER1\CWMLITE01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\DRSYS01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\EXAMPLE01.DBF 148.75
G:\ORACLEPDB\ORADATA\MASTER1\INDX01.DBF 25
G:\ORACLEPDB\ORADATA\MASTER1\ODM01.DBF 20
G:\ORACLEPDB\ORADATA\MASTER1\TOOLS01.DBF 10
G:\ORACLEPDB\ORADATA\MASTER1\USERS01.DBF 25
G:\ORACLEPDB\ORADATA\MASTER1\XDB01.DBF 38.125
b) Increase the system tablespace
1* alter database datafile ‘G:\ORACLEPDB\ORADATA\MASTER1\SYSTEM01.DBF’ resize 600m
SQL> /
Database altered.
c) Check the Free space again.
1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /
TABLESPACE_NAME FREE_SPACE
—————————— ———-
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 204.25
TOOLS 3.9375
UNDOTBS1 189.8125
USERS 24.9375
XDB .1875
4- Install the Replication catalog:
Oracle 9i does not required to install the Replication Catalog
5- Net9 configuration:
MASTER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master1)
)
)
MASTER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = master2)
)
)
6- Creation of Replication Administrator (REPADMIN):
SQL> create user repadmin identified by repadmin
2 default tablespace tools
3 temporary tablespace temp
4 quota unlimited on tools
5 quota unlimited on temp
6 quota 0 on system;
User created.
SQL> grant connect, resource to repadmin;
Grant succeeded.
SQL> exec dbms_repcat_admin.grant_admin_any_schema(‘REPADMIN’)
PL/SQL procedure successfully completed.
SQL> grant comment any table to repadmin;
Grant succeeded.
SQL> grant lock any table to repadmin;
Grant succeeded.
7- Creation of Replication Propagator / Receiver:
The propagator user is responsible to forward the deferred transactions from the source master site to the destination master site.
The receiver user is responsible for applying these transactions at the destination master site.
The replication administrator, propagator and receiver are normally the same user i.e. REPADMIN
Register the REPADMIN as the propagator
SQL> execute dbms_defer_sys.register_propagator(‘REPADMIN’)
PL/SQL procedure successfully completed. SQL> grant execute any procedure to repadmin;
Grant succeeded.
8- Creation of Schema Owner
This user is usually responsible for the day-to-day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN.
SQL> ed
Wrote file afiedt.buf
1 create user repdba identified by repdba
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 quota unlimited on temp
6* quota 0 on system
SQL> /
User created.
9- Create Database Links
All databases participating in the Replication must have a unique global name. The global name should be the same as the database name.
a) Check the global_name
SQL> select *
2 from global_name
3 ;
GLOBAL_NAME
——————————————————–
MASTER1.US.ORACLE.COM
b) Change the global_name
SQL> update global_name
2 set global_name=’MASTER1′;
1 row updated.
SQL> commit;
Commit complete.
c) Check the global_name
SQL> select *
2 from global_name;
GLOBAL_NAME
—————————————
MASTER1
10- Create the Public Database Link
a) Check the database links in the database
SQL> select *
2 from dba_db_links;
no rows selected
b) Create the public database link
1 create public database link master2
2* using ‘master2’
SQL> /
Database link created.
c) Check the database link
1 select *
2* from dba_db_links
SQL> /
OWNER DB_LINK USERNAME HOST CREATED
—————————— ——————– —————————— ———- ———
PUBLIC MASTER2 master2 15-MAY-04
11- Create the Private Database Link for REPADMIN
a) Create the private database link by connecting as REPADMIN
1 create database link master2
2* connect to repadmin identified by repadmin
3 /
Database link created.
b) Check the database link
SQL> l
1 select *
2* from dba_db_links
SQL> /
OWNER DB_LINK USERNAME HOST CREATED
———- ——————– —————————— ———- ———
PUBLIC MASTER2 master2 17-MAY-04
REPADMIN MASTER2 REPADMIN 17-MAY-04
12- Schedule “push” and “purge” jobs:
a) Connect with REPADMIN
b) Set up the “push” job to push the deferred queue automatically.
1 begin
2 dbms_defer_sys.schedule_push (
3 DESTINATION => ‘master2’,
4 INTERVAL => ‘/*1:Mins*/ sysdate + 10/(60*24)’,
5 NEXT_DATE => sysdate,
6 STOP_ON_ERROR => FALSE,
7 delay_seconds => 0,
8 PARALLELISM => 1);
9* end;
SQL> /
PL/SQL procedure successfully completed.
c) Set up the “purge” job to purge the deferred queue automatically.
SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date => sysdate,
4 interval => ‘/*1:Hr*/ sysdate + 1/24’,
5 delay_seconds => 0,
6 rollback_segment => ”);
7 end;
8 /
PL/SQL procedure successfully completed.
On Master2 Database:
1- Check the init parameters
SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 9.2.0.0.0
It should be same as the Oracle 9i Release which is being used for the Replication
b) SQL> show parameter shared_pool_size
NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 50331648
Add 110MB in the shared_pool_size
c) SQL> SQL> show parameter processes
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
Add 12 to the processes
d) SQL> SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
It should be TRUE
e) SQL> SQL> show parameter db_domain
NAME TYPE VALUE
———————————— ———– ——————————
db_domain string world
It is the extension component of the local databases Global Name
f) SQL> SQL> show parameter open_links
NAME TYPE VALUE
———————————— ———– ——————————
open_links integer 4
open_links_per_instance integer 4
Add 2 for each master site into open_links
g) distributed_transaction * need to be clarify
h) SQL> SQL> show parameter replication
NAME TYPE VALUE
———————————— ———– ——————————
replication_dependency_tracking boolean TRUE
i) SQL> show parameter job_queue
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 10
Add 1 per additional Master
k) SQL> SQL> show parameter servers
NAME TYPE VALUE
———————————— ———– ——————————
parallel_max_servers integer 5
parallel_min_servers integer 0
set parallel_max_servers to 10
set parallel_min_servers to 2
2- Check the init parameters after modifications
a) SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 9.2.0.0.0
b) SQL> SQL> show parameter shared_pool_size
NAME TYPE VALUE
———————————— ———– ——————————
shared_pool_size big integer 167772160
c) SQL> SQL> show parameter processes
NAME TYPE VALUE
———————————— ———– ——————————
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 11
log_archive_max_processes integer 2
processes integer 170
d) SQL> SQL> show parameter global_names
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean TRUE
e) SQL> SQL> show parameter db_domain ***
NAME TYPE VALUE
———————————— ———– ——————————
db_domain string
f) SQL> SQL> show parameter open_links
NAME TYPE VALUE
———————————— ———– ——————————
open_links integer 6
open_links_per_instance integer 4
g) SQL> SQL> show parameter replication
NAME TYPE VALUE
———————————— ———– ——————————
replication_dependency_tracking boolean TRUE
h) SQL> SQL> show parameter job
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 11
i) SQL> SQL> show parameter parallel_max_servers
NAME TYPE VALUE
———————————— ———– ——————————
parallel_max_servers integer 10
SQL> SQL> show parameter parallel_min_servers
NAME TYPE VALUE
———————————— ———– ——————————
parallel_min_servers integer 2
3- Check the Tablespace Free Space
1 select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3* group by tablespace_name
SQL> /
TABLESPACE_NAME FREE_SPACE
—————————— ———-
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 4.25
TOOLS 3.9375
UNDOTBS1 194.375
USERS 24.9375
XDB .1875
System Tablespace should have 80MB free space
Undo Tablespace should have 60 MB free space
3.1) Increase the required space
b) Check the total space for each tablespace in database
SQL> l
1 1 select file_name,bytes/1024/1024
2* from dba_data_files
SQL> /
FILE_NAME BYTES/1024/1024
——————————————————- —————
F:\ORACLESB\ORADATA\MASTER2\SYSTEM01.DBF 400
F:\ORACLESB\ORADATA\MASTER2\UNDOTBS01.DBF 200
F:\ORACLESB\ORADATA\MASTER2\CWMLITE01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\DRSYS01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\EXAMPLE01.DBF 148.75
F:\ORACLESB\ORADATA\MASTER2\INDX01.DBF 25
F:\ORACLESB\ORADATA\MASTER2\ODM01.DBF 20
F:\ORACLESB\ORADATA\MASTER2\TOOLS01.DBF 10
F:\ORACLESB\ORADATA\MASTER2\USERS01.DBF 25
F:\ORACLESB\ORADATA\MASTER2\XDB01.DBF 38.125
b) Increase the system tablespace
SQL> alter database datafile ‘F:\ORACLESB\ORADATA\MASTER2\SYSTEM01.DBF’ resize 600m;
Database altered.
c) SQL> select tablespace_name,sum(bytes)/1024/1024 Free_Space
2 from dba_free_space
3 group by tablespace_name
4 /
TABLESPACE_NAME FREE_SPACE
—————————— ———-
CWMLITE 10.625
DRSYS 10.3125
EXAMPLE .125
INDX 24.9375
ODM 10.6875
SYSTEM 204.25
TOOLS 3.9375
UNDOTBS1 194.375
USERS 24.9375
XDB .1875
4- Install the Replication catalog:
Oracle 9i does not required to install the Replication Catalog
5- Net9 configuration:
MASTER2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = master2)
)
)
MASTER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.0.71)(PORT = 1526))
)
(CONNECT_DATA =
(SERVICE_NAME = master1)
)
)
6- Creation of Replication Administrator (REPADMIN):
1 create user repadmin identified by repadmin
2 default tablespace tools
3 temporary tablespace temp
4 quota unlimited on tools
5 quota unlimited on temp
6* quota 0 on system
SQL> /
User created.
SQL> grant connect, resource to repadmin;
Grant succeeded.
SQL> exec dbms_repcat_admin.grant_admin_any_schema(‘REPADMIN’)
PL/SQL procedure successfully completed.
SQL> grant comment any table to repadmin;
Grant succeeded.
SQL> grant lock any table to repadmin;
Grant succeeded.
7- Creation of Replication Propagator / Receiver:
The propagator user is responsible to forward the deferred transactions from the source master site to the destination master site.
The receiver user is responsible for applying these transactions at the destination master site.
The replication administrator, propagator and receiver are normally the same user i.e. REPADMIN
Register the REPADMIN as the propagator
SQL> execute dbms_defer_sys.register_propagator(‘REPADMIN’)
PL/SQL procedure successfully completed. SQL> grant execute any procedure to repadmin;
Grant succeeded.
8- Creation of Schema Owner
This user is usually responsible for the day-to-day administration of the schema that replication objects are created upon and can be the same user or a separate user from REPADMIN.
SQL> ed
Wrote file afiedt.buf
1 create user repdba identified by repdba
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users
5 quota unlimited on temp
6* quota 0 on system
SQL> /
User created.
9- Create Database Links
All databases participating in the Replication must have a unique global name. The global name should be the same as the database name.
a) Check the global_name
SQL> select *
2 from global_name
3 ;
GLOBAL_NAME
——————————————————–
MASTER2
10-Create the Public Database Link
a) Check the database links in the database
SQL> select *
2 from dba_db_links;
no rows selected
b) Create the public database link
1 create public database link master1
2* using ‘master1’
SQL> /
Database link created.
c) Check the database link
1 select *
2* from dba_db_links
SQL> /
OWNER DB_LINK USERNAME HOST CREATED
———- —————————— —————————— ———- ———
PUBLIC MASTER1 master1 03-DEC-04
11-Create the Private Database Link for REPADMIN
a) Create the private database link by connecting as REPADMIN
1 create database link master1
2* connect to repadmin identified by repadmin
3 /
Database link created.
b) Check the database link
SQL> l
1 SQL> select *
2 from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
———- —————————— —————————— ———- ———
PUBLIC MASTER1 master1 03-DEC-04
REPADMIN MASTER1 REPADMIN 17-MAY-04
12-Schedule “push” and “purge” jobs:
a) Connect with REPADMIN
b) Set up the “push” job to push the deferred queue automatically.
1 begin
2 dbms_defer_sys.schedule_push (
3 DESTINATION => ‘master1’,
4 INTERVAL => ‘/*10:Mins*/ sysdate + 10/(60*24)’,
5 NEXT_DATE => sysdate,
6 STOP_ON_ERROR => FALSE,
7 delay_seconds => 0,
8 PARALLELISM => 1);
9* end;
SQL> /
PL/SQL procedure successfully completed.
c) Set up the “purge” job to purge the deferred queue automatically.
SQL> begin
2 dbms_defer_sys.schedule_purge(
3 next_date => sysdate,
4 interval => ‘/*1:Hr*/ sysdate + 1/24’,
5 delay_seconds => 0,
6 rollback_segment => ”);
7 end;
8 /
PL/SQL procedure successfully completed.
Object Creation on Master1
a) Connect with REPDBA and create the following objects
DROP TABLE DEPT; CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY, DNAME VARCHAR2(14) , LOC VARCHAR2(13) ) ; DROP TABLE EMP; CREATE TABLE EMP (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);SQL> conn repdba/repdba@master1
Connected.
SQL> show user
USER is “REPDBA”
SQL> DROP TABLE DEPT;
DROP TABLE DEPT
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
3 DNAME VARCHAR2(14) ,
4 LOC VARCHAR2(13) ) ;
Table created.
SQL> DROP TABLE EMP;
DROP TABLE EMP
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 COMM NUMBER(7,2),
9 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
Table created.
b) Create Master Replication Object Group at Master1 by connecting as REPADMIN
1 begin
2 dbms_repcat.create_master_repgroup(
3 gname => ‘”GROUP1″‘,
4 qualifier => ”,
5 group_comment => ”);
6* end;
SQL> /
PL/SQL procedure successfully completed.
c) Add Master Database
SQL> begin
2 dbms_repcat.add_master_database(
3 gname => ‘”GROUP1″‘,
4 master => ‘master2’,
5 use_existing_objects => TRUE,
6 copy_rows => TRUE,
7 propagation_mode => ‘ASYNCHRONOUS’);
8 end;
9 /
PL/SQL procedure successfully completed.
d) Create Objects into Group i.e. “group1”
1 begin
2 dbms_repcat.create_master_repobject(
3 gname => ‘”GROUP1″‘,
4 type => ‘TABLE’,
5 oname => ‘DEPT’,
6 sname => ‘”REPDBA”‘,
7 copy_rows => TRUE,
8 use_existing_object => TRUE);
9* end;
SQL> /
PL/SQL procedure successfully completed.
1 begin
2 dbms_repcat.create_master_repobject(
3 gname => ‘”GROUP1″‘,
4 type => ‘TABLE’,
5 oname => ‘EMP’,
6 sname => ‘”REPDBA”‘,
7 copy_rows => TRUE,
8 use_existing_object => TRUE);
9* end;
SQL> /
PL/SQL procedure successfully completed.
e) Generate the Replication support
SQL> begin
2 dbms_repcat.generate_replication_support(
3 sname => ‘”REPDBA”‘,
4 oname => ‘”DEPT”‘,
5 type => ‘TABLE’,
6 min_communication => TRUE);
7 end;
8 /
PL/SQL procedure successfully completed.
1 begin
2 dbms_repcat.generate_replication_support(
3 sname => ‘”REPDBA”‘,
4 oname => ‘”EMP”‘,
5 type => ‘TABLE’,
6 min_communication => TRUE);
7* end;
SQL> /
PL/SQL procedure successfully completed.
f) Resume activity on the Master1
SQL> begin
2 dbms_repcat.resume_master_activity(
3 gname => ‘”GROUP1″‘);
4 END;
5 /
PL/SQL procedure successfully completed.
Test the Replication
a) Insert rows into DEPT at Master1 site, by connecting REPDBA
INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK’); INSERT INTO DEPT VALUES (20,’RESEARCH’,’DALLAS’); INSERT INTO DEPT VALUES (30,’SALES’,’CHICAGO’); INSERT INTO DEPT VALUES (40,’OPERATIONS’,’BOSTON’);
b) As soon as the “push” job executes at Master1 site, these inserted records will be pushed into the Master2 site. This behavior is vice versa as this is Multi Master Replication setup.
c) Insert rows into EMP at Master1 site, by connecting REPDBA
SQL> conn repdba/repdba@master1
Connected.
SQL> INSERT INTO EMP VALUES (7499,’ALLEN’,’SALESMAN’,7698,
to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);
1 row created.
SQL> commit;
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
d) Check the EMP table in Master2 site by connecting REPDBA
SQL> conn repdba/repdba@master2
Connected.
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
After 10 minutes again apply the SELECT statement.
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
The same records have been pushed to site Master2. So the Multi Master Replication
is working fine.
The same thing can be applied vice versa as well.
f) connect repdba/repdba@master2
SQL> conn repdba/repdba@master2
Connected.
g) SELECT the rows from the EMP table
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
h) Insert one new record into EMP table
1 INSERT INTO EMP VALUES (7521,’WARD’,’SALESMAN’,
2* 7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30)
SQL> /
1 row created.
SQL> commit;
Commit complete.
i) Again SELECT from emp
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
j) Now connect repdba/repdba@master1
SQL> conn repdba/repdba@master1
Connected.
k) SELECT records from the emp table
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
The third record has yet not been pushed to Master1 site. This will be pushed as soon as the “push” job is executed at Master2 site.
Again SELECT the records from EMP table.
SQL> select *
2 from emp
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
Now the same records have been pushed to site Master1 from Master2 site. So the Multi Master Replication is working fine from both the ends.
check the status of the propagated transaction:
a) Connect to Master1 as REPDBA
SQL> conn repdba/repdba@master1
Connected.
b) Insert one record into EMP
SQL> INSERT INTO EMP VALUES (7566,’JONES’,’MANAGER’,7839, to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,NULL,20);
1 row created.
SQL> commit;
c) Select the table EMP
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
d) Check the view “defcall”
SQL> select *
2 from defcall;
CALLNO DEFERRED_TRAN_ID SCHEMANAME PACKAGENAME PROCNAME ARGCOUNT
———- —————————— —————————— —————————— —————————— ———-
0 10.13.263 REPDBA EMP$RP REP_INSERT 10
e) Check the view “deftrandest”
SQL> select *
2 from deftrandest;
DEFERRED_TRAN_ID DELIVERY_ORDER DBLINK
—————————— ————– ————–
10.13.263 302468 MASTER2
f) connect to Master2 as REPDBA
SQL> conn repdba/repdba@master2
Connected.
g) Check the EMP Table
SQL> select *
2 from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
h) connect to Master1 as REPADMIN and execute the following procedure to push the transaction to the Master2
SQL> execute dbms_defer_sys.execute(‘master2’);
PL/SQL procedure successfully completed.
i) Connect to Master 2 as REPDBA
SQL> conn repdba/repdba@master2
Connected.
j) Check the EMP table
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30