Oracle Database Primary Master replication

Oracle replication is based on the table replication configuration process;

Planning:

Verify that the Oarcle database supports advanced replication features, True

>select value from V$option where parameter= ' Advanced Replicaiont '

2, whether to support Global_names

(When the Global_names parameter is set to True, the database link must have the same name as the global_name of the connected library when using Database link)

>show parameter global_names; result is true

To view the Global_name configuration:

>select * from Global_name;

3, view job_queue_processes; Between the 0-1000

>show parameter job_queue_processes;

4, View Open_links

>show parameter open_links; Default:4 cannot be changed

5. View the global database name:

>select * from Global_name;

6, set the global database name (due to string length and other reasons during the installation, the actual global_name configuration is not correct, the installation changes can be completed.) Otherwise the Dblink is not connected):

>alter database rename global_name to masterone.test.com;

>alter database rename global_name to mastertwo.test.com;

1, create the database, the global database name is: masterone.test.com/mastertwo.test.com serivce Name: masterone.test.com/mastertwo.test.com2, Tnsnames.ora Configure client Access in two databases: the configuration content is as follows: Mastertwo=(DESCRIPTION=(ADDRESS= (PROTOCOL = TCP) (HOST = mastertwo.test.com) (PORT = 1521)) (Connect_data=(SERVER=dedicated) (Service_Name=mastertwo.test.com )) Masterone=(DESCRIPTION=(ADDRESS= (PROTOCOL = TCP) (HOST = masterone.test.com) (PORT = 1521)) (Connect_data=(SERVER=dedicated) (Service_Name=masterone.test.com )))3, open the SQL Plus Test link (guaranteed database connectivity)>connect [email protected]; >connect [email protected];4, create replication admin, connect masterone.test.com database using System;5, create a repadmin execute command for the create user repadmin identified by repadmin;6, giving repadmin users the ability to create and manage replication Environments Exec Dbms_repcat_admin.grant_admin_any_schema (username= ' repadmin ');    Grant Comment any table to repadmin;    Grant lock any table to repadmin; If you use the OEM management tool, you also need to execute the following command: GRANT SELECT any DICTIONARY to repadmin;7, register repadmin as the Propagate function Exec dbms_defer_sys.register_propagator (username= ' repadmin ');8, register repadmin as the Accept function BEGIN dbms_repcat_admin. Register_user_repgroup (username= ' repadmin ', Privilege_type= ' receiver ', List_of_gnames=NULL); END;9, in order to maintain the size of the deferred transaction queue during the check, you should clear the deferred transaction that has completed successfully. The schedule_purge process automatically clears the process for you.        Use repadmin login database Connect repadmin; BEGIN Dbms_defer_sys. Schedule_purge (Next_date=sysdate, Interval= ' sysdate + 1/24 ', Delay_seconds= 0); END;10, perform the same operation on MASTERTWO.MOR.CR 4-9;11, create a dblink Connect [email protected] on masterone.test.com using System PublicDatebase link mastertwo.test.com using ' mastertwo ';        Connect [email protected] Create Database link mastertwo.test.com connect to repadmin identified by repadmin;    Create a shared dblink Connect [email protected] on mastertwo.test.com using System; Create PublicDatabase link masterone.test.com using ' masterone ';    Connect [email protected] Create Database link masterone.mor.cr connect to repadmin identified by repadmin; 12, use repadmin login masterone.test.com/mastertwo.test.com Connect [email protected] BEGIN Dbms_defer_sys. Schedule_push (Destination= ' mastertwo.test.com ', Interval= ' sysdate + (1/144) ', Next_date=sysdate, parallelism= 1, Execution_seconds= 1500, Delay_seconds= 1200);/*delay time can be set to a smaller point*/END; Connect [email protected] BEGIN Dbms_defer_sys. Schedule_push (Destination= ' masterone.test.com ', Interval= ' sysdate + (1/144) ', Next_date=sysdate, parallelism= 1, Execution_seconds= 1500, Delay_seconds= 1200);    END; 13, create the master group before you create the synchronization group, you should ensure that each database in the replication environment has the same schema and table structure.        For example, the schema defined in the test environment is HR, and the table structure is test only on the master site: masterone.test.com as master site Connect [email protected] Exec Dbms_repcat.create_master_repgroup (' Hrgroup ')14, add the object BEGIN Dbms_repcat to the master group. Create_master_repobject (Gname= ' Hrgroup ', type= ' TABLE ', Oname= ' Test ', Sname= ' HR ', Use_existing_object=TRUE, Copy_rows=FALSE);    END; 15, add the additional master site BEGIN Dbms_repcat. Add_master_database (Gname= ' Hrgroup ', Master= ' mastertwo.test.com ', Use_existing_objects=TRUE, Copy_rows=FALSE, Propagation_mode= ' Asynchronous ');        END; Use the following command to check if a connection to two databases is successful SELECT DBLINK from Dba_repsites WHERE gname= ' Hrgroup '; Mastertwo.test.com masterone.test.com16, add replication support BEGIN Dbms_repcat. Generate_replication_support (sname= ' HR ', Oname= ' Test ', type= ' TABLE ', Min_communication=TRUE);        END; Complete the check through the command check, return 0 to proceed with SELECT COUNT (*) from Dba_repcatlog WHERE gname = ' Hrgroup ';17, enable replication BEGIN dbms_repcat. Resume_master_activity (Gname= ' Hrgroup '); END;18, the test is masterone.test.com on the Hr.test table by using the add-and-remove operation, Insert into hr.test values (1, ' TestUser1 '); Commit, (do not forget to commit on SQL Plus) because the replication delay is 1200, milliseconds, so it won't be synced to mastertwo.test.com quickly .

Reference Address: https://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmastergroup.htm#i1004613

Oracle Database Primary Master replication