Oracle Database Synchronization Technology

Oracle Database Synchronization Technology

Advanced Replication
What is replication? Simply put, replication is the process of copying data in a distributed database environment consisting of two or more database systems.
Advanced Replication is the process of copying and maintaining database objects in multiple databases that constitute a distributed database system. Oracle Advanced Replication allows applications Program Update any copies of the database and automatically pass these changes to other databases, while ensuring global transaction processing consistency and data integrity.
Synchronous replication: the copied data is consistent at any time on any replication node. If the replication data of any node in the replication environment is updated, this change is immediately reflected to all other replication nodes. This technology applies to commercial applications with high real-time requirements.
During asynchronous replication, data on all replication nodes is not synchronized for a certain period of time. If the replication data of one of the nodes in the replication environment is updated, this change will be propagated and applied to all other replication nodes in different transactions. The interval between these different transactions can be several seconds, several minutes, several hours, or several days later. Data between replication nodes is temporarily not synchronized, but the propagation will eventually ensure data consistency between all replication nodes.
I. Prerequisites for Database Replication
1. The database supports advanced replication.
You can log on to the database as a system and view the V $ option view. If Advanced Replication is true, Advanced Replication is supported. Otherwise, Advanced Replication is not supported.
2. Database initialization parameter requirements
① Db_domain = test.com.cn
Specifies the Domain Name of the database (World by default). Here, you can use the domain name of your company.
② Global_names = true
It requires that the database link be the same as the name of the connected database.
Now the global database name is db_name + "." + db_domain
③ There are parameters related to database job execution
Job_queue_processes = 1
Job_queue_interval = 60
Distributed_transactions = 10
Open_links = 4
The first line defines the number of initiation of the SNP process as N. The default value is 0, and the normal definition range is 0 ~ 36. You can configure different values based on the number of tasks.
The second line defines the system to wake up the process every n seconds. The default value is 60 seconds. The normal range is 1 ~ 3600 seconds. In fact, the process enters the sleep state after the current task is executed. After a period of sleep, the general control of the system will wake it up.
If you have modified these parameters, You need to restart the database to make the parameters take effect.
2. Steps for Synchronous Database Replication
Suppose we have two databases on the Internet: Shenzhen and Beijing ).
The specific configuration is as follows:
Database Name Shenzhen Beijing
Database domain name test.com.cn
Database Sid no. Shenzhen Beijing
Listener port 1521 1521
Server IP address 10.1.1.100 10.1.1.200

1. confirm that the two databases can access each other and set the database connection string in tnsnames. ora.
①. For example, the database connection string in Shenzhen is in the following format:
Beijing =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = 10.1.1.200) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = Beijing)
)
)
Run $ tnsping Beijing
The following prompt appears:
Attempting to contact (address = (Protocol = TCP) (host = 10.1.1.200) (Port = 1521 ))
OK (N ms)
Indicates that the Shenzhen database can access the Beijing database.
(2) The configuration is also configured in Beijing. Make sure that $ tnsping Shenzhen is accessible.
2. Change the global name of the database and create a public database link.
1. log on to the Shenzhen database as a system user
SQL> alter database rename global_name to shenzhen.test.com.cn;
Log on to the Beijing database as a system:
SQL> alter database rename global_name to beijing.test.com.cn;
② Log on to the Shenzhen database as a system user
SQL> Create public database link beijing.test.com.cn using 'beijing ';
Test the global database name and public database connection.
SQL> select * From global_name@beijing.test.com.cn;
The returned result is beijing.test.com.cn.
Log on to the Beijing database as a system:
SQL> Create public database link shenzhen.test.com.cn using 'shenzhen ';
Test the global database name and public database connection.
SQL> select * From global_name@shenzhen.test.com.cn;
The returned result is shenzhen.test.com.cn.
3. Create a user repadmin who manages database replication and grant permissions.
1. log on to the Shenzhen database as a system user
SQL> create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
SQL> execute dbms_defer_sys.register_propagator ('repadmin ');
SQL> grant execute any procedure to repadmin;
SQL> execute dbms_repcat_admin.grant_admin_any_repgroup ('repadmin ');
SQL> grant comment any table to repadmin;
SQL> grant lock any table to repadmin;
② Log on to the Beijing database as a system, run the preceding commands, manage the repadmin user for database replication, and grant permissions.
Note: The repadmin user name and password can be freely named as needed.
4. Create a private database link under the user repadmin who copied the database.
① Log on to the Shenzhen database as a repadmin user
SQL> CREATE DATABASE LINK beijing.test.com.cn connect to repadmin identified by repadmin;
Test the private database link:
SQL> select * From global_name@beijing.test.com.cn;
The returned result is beijing.test.com.cn.
② Log on to the Beijing database as repadmin
SQL> CREATE DATABASE LINK shenzhen.test.com.cn connect to repadmin identified by repadmin;
Test the private database link.
SQL> select * From global_name@shenzhen.test.com.cn;
The returned result is shenzhen.test.com.cn.
5. Create or select users and objects for database replication to grant permissions to users. database objects must have primary keywords.
Suppose we use the Scott user and dept table used in Oracle as an example.
① Log on to the Shenzhen database as internal, create a Scott user, and grant permissions
SQL> create user Scott identified by tiger default tablespace users temporary tablespace temp;
SQL> grant connect, resource to Scott;
SQL> grant execute on SYS. dbms_defer to Scott;
② Log on to the Shenzhen database as Scott and create the table Dept
SQL> Create Table Dept
(Deptno number (2) primary key,
Dname varchar2 (14 ),
Loc varchar2 (13 ));
③ If the database object does not have a primary keyword, run the following SQL command to add it:
SQL> ALTER TABLE dept add (constraint dept_deptno_pk primary key (deptno ));
④ Create the serial number of the primary keyword under the Scott user in the Shenzhen database to avoid conflict with Beijing.
SQL> Create sequence dept_no increment by 1 start with 1 maxvalue 44 cycle nocache;
(Note: maxvalue 44 can be determined based on the number of digits defined by the application and table structure master keywords)
⑤ Insert initialization data under the Scott user in the Shenzhen Database
SQL> insert into dept values (dept_no.nextval, 'accounting', 'New York ');
SQL> insert into dept values (dept_no.nextval, 'Research ', 'Dallas ');
SQL> commit;
6. The Beijing database also runs above ①, ②, ③
7. Create the serial number of the primary keyword under the Scott user of the Beijing database to avoid conflict with Shenzhen.
SQL> Create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache;
Inserting initialization data under the Scott user of the Beijing Database
SQL> insert into dept values (dept_no.nextval, 'sales', 'Chicago ');
SQL> insert into dept values (dept_no.nextval, 'operations', 'boston ');
SQL> commit;
6. Create the scott_mg group to be copied and add it to the database object to generate object replication support
1. log on to the Shenzhen database as repadmin and create the master replication group scott_mg.
SQL> execute dbms_repcat.create_master_repgroup ('Scott _ Mg ');
Note: The scott_mg group name can be freely named as needed.
② Add database objects to the replication group scott_mg
SQL> execute dbms_repcat.create_master_repobject (sname => 'Scott ', oname => 'dept', type => 'table', use_existing_object => true, gname => 'Scott _ Mg ');
Parameter description:
User name for Database Replication Using sname
Oname: name of the database object for Database Replication
(The table name is within 27 bytes and the package name is within 24 bytes)
Type: specifies the type of the database object used for database replication.
(Supported categories: tables, indexes, synonyms, triggers, views, processes, functions, packages, and package bodies)
Use_existing_object true indicates that the database object already exists on the master replication Node
Gname master replication group name
③ Support for database object Replication
SQL> execute dbms_repcat.generate_replication_support ('Scott ', 'dept', 'table ');
(Note: generate database triggers and packages that support replication of dept tables under Scott users)
④ Confirm that the copied group and object have been added to the data dictionary of the database
SQL> select gname, Master, status from dba_repgroup;
SQL> select * From dba_repobject;
7. Create a master replication Node
1. log on to the Shenzhen database as repadmin and create a master replication node.
SQL> execute dbms_repcat.add_master_database
(Gname => 'Scott _ Mg ', Master => 'Beijing .test.com.cn', use_existing_objects => true, copy_rows => false, propagation_mode => 'asynchronous ');
Parameter description:
Gname master replication group name
Another database in which the master node joins the master replication Node
Use_existing_object true indicates that the database object already exists on the master replication Node
Copy_rows false indicates that the primary replication node does not need to be consistent with the primary replication node when the replication starts for the first time.
Propagation_mode asynchronous execution
② Confirm that the copied task queue has been added to the data dictionary of the database
SQL> select * From user_jobs;
8. Change the status of the synchronization group from pause (quiesced) to normal (normal)
1. log on to the Shenzhen database as repadmin and run the following command:
SQL> execute dbms_repcat.resume_master_activity ('Scott _ Mg ', false );
② Confirm that the synchronization group is in normal status)
SQL> select gname, Master, status from dba_repgroup;
③ If the ① command cannot make the synchronization group State Normal, there may be some paused copies. Run the following command and try again (it is recommended that you use it in an emergency ):
SQL> execute dbms_repcat.resume_master_activity ('Scott _ Mg ', true );
9. Create a database copy schedule. Suppose we use a fixed schedule: Copy once every 10 minutes.
1. log on to the Shenzhen database as repadmin and run the following command:
SQL> begin
Dbms_defer_sys.schedule_push (
Destination => 'Beijing .test.com.cn ',
Interval => 'sysdate + 123 ',
Next_date => sysdate );
End;
/

SQL> begin
Dbms_defer_sys.schedule_purge (
Next_date => sysdate,
Interval => 'sysdate + 123 ',
Delay_seconds => 0,
Rollback_segment => '');
End;
/

② Log on to the Beijing database as repadmin and run the following command
SQL> begin
Dbms_defer_sys.schedule_push (
Destination => 'shenzhen.test.com.cn ',
Interval => 'sysdate + 123 ',
Next_date => sysdate );
End;
/

SQL> begin
Dbms_defer_sys.schedule_purge (
Next_date => sysdate,
Interval => 'sysdate + 123 ',
Delay_seconds => 0,
Rollback_segment => '');
End;
/
10. Add or modify the records of databases on both sides to track the replication process
If you want to immediately see the changes in the database records after the addition or modification, you can find the job_number of Push under the repadmin user on both sides, and then run:
SQL> exec dbms_job.run (job_number );
Iii. Handling exceptions
1. Check whether the replication works normally. You can query user_jobs under the repadmin user.
SQL> select job, this_date, next_date, what, broken from user_jobs;
There are two normal statuses:
Task idle -- this_date is null, and next_date is a time value after the current time
Task busy -- this_date is not empty, and next_date is a time value after the current time
There are two exception statuses:
Task deadlock -- next_date is a time value before the current time
Task deadlock -- next_date is a very large time value, for example, 4001-01-01
This may be caused by a deadlock caused by network interruptions.
How to remove the deadlock:
$ PS-Ef | grep Orale
Locate the process number ora_snp * of the refresh snapshot of the deadlock and run the kill-9 command to delete the process.
Go to the repadmin user SQL> operator and run the following command:
SQL> exec dbms_job.run (job_number );
Note: job_number indicates the job number identified by the Select job, this_date, next_date, And what from user_jobs command.
2. add or remove replication objects in the replication group
① Stop the replication of the master database node so that the synchronization group status changes from normal to quiesced)
Log on to the Shenzhen database as repadmin and run the following command:
SQL> execute dbms_repcat.suspend_master_activity (gname => 'Scott _ Mg ');
② Add database objects to the replication group scott_mg to ensure that database objects must have primary keywords.
SQL> execute dbms_repcat.create_master_repobject (sname => 'Scott ', oname => 'emp', type => 'table', use_existing_object => true, gname => 'Scott _ Mg ');
Replication support for the added database objects
SQL> execute dbms_repcat.generate_replication_support ('Scott ', 'emp', 'table ');
③ Delete database objects in the replication group scott_mg.
SQL> execute dbms_repcat.drop_master_repobject ('Scott ', 'dept', 'table ');
④ Reset the status of the synchronization group from pause (quiesced) to normal ).
SQL> execute dbms_repcat.resume_master_activity ('Scott _ Mg ', false );